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

• david.gugg

  SSCertifiable

  Points: 6143

  More actions

  May 22, 2014 at 7:13 am

  #1715681

  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

  May 22, 2014 at 7:33 am

  #1715693

  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: 286602

  More actions

  May 22, 2014 at 8:14 am

  #1715712

  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

  May 22, 2014 at 10:42 pm

  #1715938

  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