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?

>Solution :

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