Help with Case Statement

Question

Post reply

Help with Case Statement

mak_crystal07

Grasshopper

Points: 18
More actions
August 4, 2009 at 8:01 am

#209044

Hello,
I have the following code in my stored procedure:
[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!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

ironically, it's easy...you just wrap the whole thing to be a sub query:

SELECT ISNULL(CTTitle1,CTTitle) As TheTitle

From

(

SELECT

[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))

) MyAlias --the wrapped query

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mak_crystal07 Grasshopper Points: 18 More actions · Answer 2

HI,

Thanks for your reply. I just included the code in my select statement as follows but I am getting errors as

Incorrect syntax near the keyword 'SELECT'.

Incorrect syntax near the keyword 'CASE'.

SELECTDISTINCT

CASE

WHEN DonorCompanyID -1 THEN d.OrderID + d.DonorCompanyID

ELSE d.ID

END REFERENCE,

CASE

WHEN DonorCompanyID -1 THEN d.OrderID

ELSE d.ID

END REFERENCE1,

o.OriginalOrderID,

d.Trandate,

d.donorcompanyId,

c.Name AS CName,

c.LCDistrictName,

od.ShipToID,

od.shiptoname,

SELECT ISNULL(CTTitle1,CTTitle) As TheTitle

From

(

SELECT

[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))

) MyAlias, --the wrapped query

FROM ...................................................................

Any suggestions please.

Thank You!

kam7395 Old Hand Points: 395 More actions · Answer 3

I am sorry I did not mention that I am using SQL Server 2000

Lowell SSC Guru Points: 323518 More actions · Answer 4

you did not paste your entire query, so you have to extrapolate:

take your entire query from your stored proc, not just the case statement, and wrap it in parenthsis.

give it an alias.

add SELECT to the top of it:

for example:

SELECT ISNULL(CTTitle1,CTTitle) As TheTitle,

LotsOfOtherOtherColumns from

( SELECT lotsofstuff

.

--400 columns later

From BigHonkingQuery

INNER JOIN

.

--many table joins later

) MyAlias

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kam7395 Old Hand Points: 395 More actions · Answer 5

Here is my query:

SELECTDISTINCT

CASE

WHEN DonorCompanyID -1 THEN d.OrderID + d.DonorCompanyID

ELSE d.ID

END REFERENCE,

CASE

WHEN DonorCompanyID -1 THEN d.OrderID

ELSE d.ID

END REFERENCE1,

o.OriginalOrderID,

d.Trandate,

d.donorcompanyId,

c.Name AS CName,

c.LCDistrictName,

od.ShipToID,

od.shiptoname,

CASE WHEN c.BillingCountry LIKE '%CHINA%' OR c.BillingCountry LIKE '%KOREA%' THEN pa1.AddressLine1

ELSE od.shiptoaddrline1 --by kam

END AddressLine1, --needs to go to DG Address if CHINA or KOREA

CASE WHEN c.BillingCountry LIKE '%CHINA%' OR c.BillingCountry LIKE '%KOREA%' THEN pa1.AddressLine2

ELSE od.shiptoaddrline2 --by kam

END AddressLine2,

CASE WHEN c.BillingCountry LIKE '%CHINA%' OR c.BillingCountry LIKE '%KOREA%' THEN pa1.AddressLine3

ELSE od.shiptoaddrline3 --by kam

END AddressLine3,

CASE WHEN c.BillingCountry LIKE '%CHINA%' OR c.BillingCountry LIKE '%KOREA%' THEN pa1.City

ELSE od.shiptocity --by kam

END City,

CASE WHEN c.BillingCountry LIKE '%CHINA%' OR c.BillingCountry LIKE '%KOREA%' THEN pa1.State

ELSE od.shiptostate --by kam

END State,

CASE WHEN c.BillingCountry LIKE '%CHINA%' OR c.BillingCountry LIKE '%KOREA%' THEN pa1.ZipCode

ELSE od.shiptozipcode --by kam

END ZipCode,

CASE WHEN c.BillingCountry LIKE '%CHINA%' OR c.BillingCountry LIKE '%KOREA%' THEN pa1.Country

ELSE od.shiptocountry --by kam

END Country,

cmc.MemberCount,

od.LCHoldShipment,

SELECT ISNULL(CTTitle,CTTitle1) As TheTitle

From

(

SELECT

[CTTitle] =(SELECT TOP 1TITLE

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)) --od.ShipToID

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 1TITLE

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))

)x --the wrapped query

INTO #ContrbMems

FROM #Donations d

INNER JOIN mms.dbo.vwOrders o ON o.ID = d.OrderID

INNER JOIN mms.dbo.vwCompanies c ON c.ID = d.DonorCompanyID

--INNER JOIN mms.dbo.vwproducts p ON d.productid = p.ID

INNER JOIN mms.dbo.vworderdetails od ON od.OrderID = d.OrderID

AND od.Sequence = d.OrderLineID

LEFT OUTER JOIN #CLUBCMEMBERSSHIPTO cmst ON cmst.OrderID = d.OrderID AND cmst.DonorClubID = d.DonorCompanyID

LEFT OUTER JOIN mms.dbo.vwpersonaddresses pa ON pa.personid =(SELECT pl.PresID FROM #PresidentList pl WHERE pl.ClubCTID = (SELECT TOP 1 ID FROM mms.dbo.vwcommitteeterms ct WHERE ct.lccompanyid = d.donorcompanyId))

AND PA.AddressTypeID = 25

LEFT OUTER JOIN mms.dbo.vwpersonaddresses pa1 ON pa1.personid =(SELECT TOP 1 gl.DGOVID FROM #DGOVLIST gl WHERE gl.ClubCTID = (SELECT TOP 1 ID FROM mms.dbo.vwcommitteeterms ct WHERE ct.lccompanyid = c.LCDistrictId)) --changed to Top 1

AND PA1.AddressTypeID = 25

--INNER JOIN mms.dbo.vwpersons pe ON pe.ID = (SELECT TOP 1 pl.PresID FROM #PresidentList pl --Changed to Top 1

LEFT OUTER JOIN mms.dbo.vwpersons pe ON pe.ID = (SELECT TOP 1 pl.PresID FROM #PresidentList pl WHERE pl.ClubCTID = (SELECT TOP 1 ID FROM mms.dbo.vwcommitteeterms ct WHERE ct.lccompanyid = d.donorcompanyId)) --Changed to Top 1 by kam

LEFT OUTER JOIN #CLUBCMEMBERSCT cmc ON cmc.reference = d.OrderID AND cmc.CLub = d.DonorCompanyID-- > 0

WHERE cmc.MemberCount > 1 AND O.OrderTypeID 3

SELECT * FROM #ContrbMems

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 6

Try This :

SELECT DISTINCT CASE

WHEN DonorCompanyID -1

THEN d.OrderID

+ d.DonorCompanyID

ELSE d.ID

END REFERENCE,

CASE

WHEN DonorCompanyID -1

THEN d.OrderID

ELSE d.ID

END REFERENCE1,

o.OriginalOrderID,

d.Trandate,

d.donorcompanyId,

c.Name AS CName,

c.LCDistrictName,

od.ShipToID,

od.shiptoname,

CASE

WHEN c.BillingCountry LIKE '%CHINA%'