CASE WHEN field is NULL

1

Hello,

I could greatly use your help with this query.

SELECT
CASE WHEN b.TransferToLocationFKey is null then b.TransferToPerson else d.locationname end as
'Location'
FROM EvidenceChainOfCustody b
JOIN location d on b.TransferToLocationFKey = d.uniquekey
WHERE b.EvidenceFKey = '23420sdfjl'

As you can see, the value can be pulled from 2 different tables. There are three records which should return. But it isn't returning the record where the b.TransferToLocationFKey is null.

Truly appreciate your help

2

NULL is never "=" to anything, so the INNER JOIN on a NULL value will remove the row.

You need to use LEFT OUTER JOIN instead:
...
LEFT OUTER JOIN location d on b.TransferToLocationFKey = d.uniquekey
...

1 Like
3

Thank you Scott. That worked. I hate NULLs. haha!

4

You don't need a CASE expression - once you correct the join. You can use:

COALESCE(d.LocationName, b.TransferToPerson)