May 22, 2014 at 7:09 am
Hi There,
I have a SQL script that JOINS three tables. As part of the columns that I return I have a CASE statement. Because I am doing a LEFT JOIN, two of my table may / may not return data, instead on some rows they show NULL values. Is there a way to replace these NULL values with a different value that I define?
Thanks
May 22, 2014 at 7:13 am
You can use the ISNULL() function:
SELECT ISNULL(fieldthatmaybenull, 'Other value')
FROM ...
Be still, and know that I am God - Psalm 46:10
May 22, 2014 at 7:33 am
Thanks, I did try that, but because my CASE statement was in a nested query, it didn't work... I managed to get it working...
Here's how:
SELECT
SCH.column1
, SCH.column2
, SCH.column3
, SCH.column4
,(CASE
WHEN REG.column5 = 0 THEN 'Present'
WHEN REG.column5 = 1 THEN 'Late'
WHEN REG.column5 = 2 THEN 'Absent'
WHEN REG.column5 = 3 THEN 'AWOL'
WHEN REG.column5 IS NULL THEN 'No Status Captured'
END) AS status
FROM
tablenameSCH
LEFT JOIN ...
May 22, 2014 at 8:14 am
lclaassen (5/22/2014)
Thanks, I did try that, but because my CASE statement was in a nested query, it didn't work... I managed to get it working...Here's how:
SELECT
SCH.column1
, SCH.column2
, SCH.column3
, SCH.column4
,(CASE
WHEN REG.column5 = 0 THEN 'Present'
WHEN REG.column5 = 1 THEN 'Late'
WHEN REG.column5 = 2 THEN 'Absent'
WHEN REG.column5 = 3 THEN 'AWOL'
WHEN REG.column5 IS NULL THEN 'No Status Captured'
END) AS status
FROM
tablenameSCH
LEFT JOIN ...
FYI, you can still get a NULL in your results. If you have a row where the value of column5 is not null and not in (0,1,2,3) your case expression will still return a NULL. If you want to always return a value you should also include an ELSE condition in your case expression.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 22, 2014 at 10:42 pm
Hi Sean,
Yip thanks I do realize that, but luckily I am using an input for that only allows values 0,1,2,3 to be captured. Hence why I used the case statement as is.
Thanks
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy