August 4, 2009 at 9:13 am
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!
August 4, 2009 at 9:19 am
How's that?
Select case when CTTitle is null
then CTTitle1
else CTTitle end as CTTitle
FROM ... ...
Cheers,
J-F
August 4, 2009 at 9:26 am
Why use Case for that? Why not IsNull or Coalesce?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2009 at 9:33 am
GSquared (8/4/2009)
Why use Case for that? Why not IsNull or Coalesce?
Thanks for the correction Gus, that's the difference between someone who takes the time to answer the problem, and someone like me, who just answers what the OP wants to hear. You're right, Coalesce(Col1, Col2) would be far more intuitive.
Cheers,
J-F
August 4, 2009 at 9:55 am
Below is my entire query. Please look at the code which starts as
SELECT ISNULL(x.CTTitle,x.CTTitle1) As TheTitle
....
...
...
I am getting the syntax error as Incorrect syntax near the keyword 'SELECT'.
Please help me out.
Thank YOU!
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(x.CTTitle,x.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 queryINTO #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
August 4, 2009 at 11:05 am
Are you guys working together, or against?
http://www.sqlservercentral.com/Forums/Topic764816-1291-1.aspx?Update=1
You have practically the same code, and you ask the same questions, what's going on?
By the way, I answered to your friend there, see how I built his query to work with the isNull.
Cheers,
J-F
August 6, 2009 at 1:21 pm
Thank You!
August 6, 2009 at 1:22 pm
Thanks a lot!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply