SQL CASE when a returnedvalue = NULL

[SBSP]

So i have 2 tables.

ITEM MASTER - With Fields ITEM CODE and PRODUCT_DESCRIPTION
ITEM_TYPE - With ITEM CODE and ITEM_TYPE_CODE

Types are Body, software, Accessory, Consumable , ect ect

Not all items in the Item master as a type_code only items sold in the last 2 years have a type code.

So i'm doing a Left join from the Item_Master to the Item_Type.

So now i want to "create" a new type 0 (as in Zero) = No Type assigned.

When i run my query the Items that does not have a type displays as Null in the field.
ITEM_TYPE.ITEM_TYPE_CODE
CASE
WHEN ITEM_TYPE.ITEM_TYPE_CODE [EDIT] --> IS NULL then 'No code assigned' <--this still just returns a NULL
WHEN ITEM_TYPE.ITEM_TYPE_CODE = '1' then 'Body'
WHEN ITEM_TYPE.ITEM_TYPE_CODE = '2' then 'Lens'
WHEN ITEM_TYPE.ITEM_TYPE_CODE = '3' then 'Accesory'
WHEN ITEM_TYPE.ITEM_TYPE_CODE = '4' then 'Binoculars'
END as ITEM_TYPE_CODE

Ho do i get this right ?

Dont worry i got it right,

 

[Cool E]

Use Else to cover all other unknown entities.


CASE ITEM_TYPE.ITEM_TYPE_CODE
WHEN 1 then 'Body'
WHEN 2 then 'Lens'
WHEN 3 THEN 'Accesory'
WHEN 4 THEN 'Binoculars'
ELSE 'No code assigned'
END

 

[Nerfherder]

Try "IS NULL" not "= NULL"

 

[SBSP]

Use Else to cover all other unknown entities.


CASE ITEM_TYPE.ITEM_TYPE_CODE
WHEN 1 then 'Body'
WHEN 2 then 'Lens'
WHEN 3 THEN 'Accesory'
WHEN 4 THEN 'Binoculars'
ELSE 'No code assigned'
END

LOL never though of that as a work around , Hehe

Try "IS NULL" not "= NULL"

Yea thats what i did it works now, thanks allot.

 

[Nerfherder]

I fought with the same issue for a long time.... the thing is to remember is that null is not a value, its a state. So you can't compare it

 

[Bona Botse]

Use Else to cover all other unknown entities.


CASE ITEM_TYPE.ITEM_TYPE_CODE
WHEN 1 then 'Body'
WHEN 2 then 'Lens'
WHEN 3 THEN 'Accesory'
WHEN 4 THEN 'Binoculars'
ELSE 'No code assigned'
END

Taxi driver, eh?