CODEWITHSUNDEEP
sqlsql-server-2014
Nick
Nick

Reputation: 372

CASE statement for rows not existing on joined table

I am trying to wrap the inner CASE statements below with an outer CASE statement to output '00-00-00-00-00' if there is not a matching row found on the left-joined OtherTable C , instead what is happening is when there is not a row found in OtherTable C then it is outputting ---- (just dashes).

SELECT A.INV_ITEM_ID, 
      CASE WHEN C.INV_ITEM_ID '' THEN '00-00-00-00-00' 
      ELSE  ( CONCAT(CASE WHEN C.STORAGE_AREA like '[0-9]' 
                  THEN '0'+ C.STORAGE_AREA
                  WHEN C.STORAGE_AREA = '' THEN '00'
                  ELSE C.STORAGE_AREA END ,'-', CASE WHEN C.STOR_LEVEL_1 like '[0-9]'
                                                     THEN '0' + C.STOR_LEVEL_1
                                                     WHEN C.STOR_LEVEL_1 = '' THEN '00'
                                                     ELSE C.STOR_LEVEL_1 END , '-',
                                                CASE WHEN C.STOR_LEVEL_2 like '[0-9]'
                                                     THEN '0' + C.STOR_LEVEL_2
                                                     WHEN C.STOR_LEVEL_2 = '' THEN '00'
                                                     ELSE C.STOR_LEVEL_2 END, '-',
                                                CASE WHEN C.STOR_LEVEL_3 like '[0-9]'
                                                     THEN '0' + C.STOR_LEVEL_3
                                                     WHEN C.STOR_LEVEL_3 = '' THEN '00'
                                                     ELSE C.STOR_LEVEL_3 END, '-',
                                                CASE WHEN C.STOR_LEVEL_4 like '[0-9]'
                                                     THEN '0' + C.STOR_LEVEL_4
                                                     WHEN C.STOR_LEVEL_4 = '' THEN '00'
                                                     ELSE C.STOR_LEVEL_4 END  ) ) END
     FROM MyTable A
     LEFT OUTER JOIN OtherTable C ON C.INV_ITEM_ID = A.INV_ITEM_ID

Is there a way to achieve this without having to use a Sub-query here?

Upvotes: 0

Views: 836

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

Might be able to cut your SQL down a bit:

SELECT 
  A.INV_ITEM_ID, 
  CONCAT(
    RIGHT(CONCAT('00', C.STOR_LEVEL_1), 2), '-',
    RIGHT(CONCAT('00', C.STOR_LEVEL_2), 2), '-',
    RIGHT(CONCAT('00', C.STOR_LEVEL_3), 2), '-',
    RIGHT(CONCAT('00', C.STOR_LEVEL_4), 2)   
  ) as x

CONCAT behaves slightly differently to + with NULL. CONCAT treats null as emptystring, but + will nullify the whole expression. This, whether your column is null, a single digit or double digit, if you CONCAT it onto '00' then take the rightmost 2 you end up with a 2 digits number (00 if null, 0x if one digit, xx if 2).

Upvotes: 0

EoinS
EoinS

Reputation: 5482

If there is no match in a left join the OtherTable value will be null not ''

So when you say this:

CASE WHEN C.INV_ITEM_ID '' THEN '00-00-00-00-00'

You are checking if there is a zero length string or '' in the field c.INV_ITEM_ID

Instead you should use

CASE WHEN C.INV_ITEM_ID is null THEN '00-00-00-00-00'

You likely do not have any c.INV_ITEM_ID with string value ''. So when there is no matching data in the left join (ie the value is null) the case expression moves on and performs a concatenation. Each sub-case expression checks for c.STOR_LEVEL_1 and doesn't have a match so goes with the "else" or C.STOR_LEVEL_1. So what you actually have is

concat(null,'-',null,'-',null,'-',null,'-')

Which evaluates to ----

Upvotes: 1

Related Questions