Help with Case Statement

eastwest · August 4th, 2009, 10:20 AM

Hello,

I have the following code in my stored procedure:

Code:

[CTTitle] =(SELECT TOP 1 TITLE FROM mms.dbo.vwcommitteetermmembers WHERE ID =
      (SELECT pl.PresID FROM #PresidentList pl 
       WHERE pl.ClubCTID = (SELECT TOP 1 ID FROM mms.dbo.vwcommitteeterms ct WHERE ct.lccompanyid = d.donorcompanyId))
        AND YEAR(ENDDATE) =
                (CASE
                  WHEN MONTH(GETDATE()) IN (7,8,9,10,11,12) THEN YEAR(GETDATE()) +1
                   ELSE YEAR(GETDATE())
                END)),
  
[CTTitle1] =(SELECT  TOP 1 TITLE FROM mms.dbo.vwcommitteetermmembers WHERE MemberID  = od.ShipToID and rank=405 
          AND YEAR(ENDDATE) =
             (CASE
              WHEN MONTH(GETDATE()) IN (7,8,9,10,11,12) THEN YEAR(GETDATE()) +1
               ELSE YEAR(GETDATE())
                END))

I want to achieve if [CTTitle] is null then I want to display [CTTitle1] else [CTTitle].

Please anybody let me know how to accomplish this.

Thanks!

RobCarter · August 4th, 2009, 11:25 AM

Hi

How about trying:

Code:

 
case 
when [cttitle] is null then [cttitle1]
else [cttitle]
end

As long as you use the same criteria language in your case statement as you would in a view (i.e. [cttitle] is null rather than [cttitle] = null) then you should be fine

This also would apply if you want to compare 2 criteria on the values of [cttitle]:
e.g.

Code:

case 
when [cttitle] >1 and [cttitle] is <10 then [cttitle1] 
else [cttitle]
end

hope this helps

Rob

eastwest · August 4th, 2009, 02:08 PM

Thanks a lot!

Quote:

Originally Posted by RobCarter

Hi

How about trying:

Code:

 
case 
when [cttitle] is null then [cttitle1]
else [cttitle]
end

As long as you use the same criteria language in your case statement as you would in a view (i.e. [cttitle] is null rather than [cttitle] = null) then you should be fine

This also would apply if you want to compare 2 criteria on the values of [cttitle]:
e.g.

Code:

case 
when [cttitle] >1 and [cttitle] is <10 then [cttitle1] 
else [cttitle]
end

hope this helps

Rob

Old Pedant · August 4th, 2009, 02:13 PM

Ummm...there is a MUCH easier solution, if you are using SQL Server:

Code:

SELECT ISNULL( cttitle, cttitle1 ) AS thetitle

The ISNULL( ) function in SQL Server does *exactly* what you asked.

rstelma · August 5th, 2009, 12:48 AM

Old Pedant

You kill ...........

RobCarter · August 5th, 2009, 04:17 AM

Pedant by name, pedant by nature

Totally forgot about isnull().

eastwest · August 6th, 2009, 03:17 PM

Thank You!

Quote:

Originally Posted by Old Pedant

Ummm...there is a MUCH easier solution, if you are using SQL Server:

Code:

SELECT ISNULL( cttitle, cttitle1 ) AS thetitle

The ISNULL( ) function in SQL Server does *exactly* what you asked.

eastwest · August 6th, 2009, 03:17 PM

Thank You!

Old Pedant · August 6th, 2009, 05:19 PM

Quote:

Originally Posted by RobCarter

Pedant by name, pedant by nature

Thanks to Philip M in codingforums.com for this quote:
âFools act on imagination without knowledge, pedants act on knowledge without imaginationâ
-- Alfred North Whitehead (British Mathematician and Philosopher, 1861-1947)

My wife says that describes me to perfection.