Case condition retruning NULL value

[jasonhuibers]

Customer_Vendor expression is pulling NULL when Vendor column is null. If the Vendor Column is null then I want 'You:' to be returned...

SELECT Message,EventHistory.DateTimeStamp, Vendor,
CASE WHEN Vendor IS NULL THEN 'You:'
ELSE Vendors.Vendor_CompanyName
END AS Customer_Vendor
FROM EventHistory WITH (NOLOCK)
LEFT JOIN Vendors WITH (NOLOCK) ON Vendors.VendorCode = SUBSTRING(EventHistory.Vendor,2,LEN(EventHistory.Vendor))
WHERE EventHistory.RecordNum = 5
order by EventHistory.DateTimeStamp desc

 

[gmmastros]

Is it possible that you have some conditions (rows) where the Vendor (from the EventHistory table) is not null but the Vendors.Vendor_CompanyName is NULL?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Try

SELECT Message,EventHistory.DateTimeStamp, Vendor,
CASE WHEN Vendor IS NULL THEN 'You:'
          ELSE coalesce(Vendors.Vendor_CompanyName,'Vendor Unknown')
     END AS Customer_Vendor
FROM EventHistory WITH (NOLOCK)
LEFT JOIN Vendors WITH (NOLOCK) ON Vendors.VendorCode = SUBSTRING(EventHistory.Vendor,2,LEN(EventHistory.Vendor)) 
WHERE EventHistory.RecordNum = 5
order by EventHistory.DateTimeStamp desc

PluralSight Learning Library

 

[jasonhuibers]

This seems to work in query analyzer - I get an error in my asp code for:

Incorrect syntax near the keyword 'order'.


sql_insert = "SELECT Message,EventHistory.DateTimeStamp, Vendor, CASE WHEN Vendor IS NULL THEN 'You:' ELSE coalesce(Vendors.Vendor_CompanyName,'Vendor Unknown') END AS Customer_Vendor " & _
"FROM EventHistory WITH (NOLOCK) " & _
"LEFT JOIN Vendors WITH (NOLOCK) ON Vendors.VendorCode = SUBSTRING(EventHistory.Vendor,2,LEN(EventHistory.Vendor))" & _
"WHERE EventHistory.RecordNum = '" & RecordNum & "' & order by EventHistory.DateTimeStamp desc"

 

[markros]

Is RecordNum character or numeric? It is always a bad idea to use string concatenation for parameters instead of using parameters. Say, in your code it will be better to use

WHERE EventHistory.RecordNum = @RecordNum
ORDER BY EventHistory.DateTimeStamp DESC"

Your particular error seems to be here

"WHERE EventHistory.RecordNum = '" & RecordNum  & "' [b]&[/b] order by EventHistory.DateTimeStamp desc"

Instead it should be

"WHERE EventHistory.RecordNum = " & RecordNum  & " order by EventHistory.DateTimeStamp desc"

I also removed single quotes as I assume RecordNum to be numeric.


PluralSight Learning Library