Replace NULL Values When Joining tables

Question

Post reply

Replace NULL Values When Joining tables

lclaassen

SSC Veteran

Points: 216
More actions
May 22, 2014 at 7:09 am

#293655

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

david.gugg SSCertifiable Points: 6098 More actions · Answer 1

You can use the ISNULL() function:

SELECT ISNULL(fieldthatmaybenull, 'Other value')

FROM ...

Be still, and know that I am God - Psalm 46:10

lclaassen SSC Veteran Points: 216 More actions · Answer 2

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

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/

lclaassen SSC Veteran Points: 216 More actions · Answer 4

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