Link to home
Start Free TrialLog in
Avatar of ONYX
ONYX

asked on

Using CASE in a where clause

Is this possible?

Here is the where clause in my query:

a_gatetxn.trailerid = a_yardcheck.trailerid

let's say that a_gatetxn.trailerid = '' or is null...if that were the case, then it would not have a matching trailerid in the a_yardcheck table (a_yardcheck.trailerid). Is it possible to say when the a_Gatetxn.trailerid is null or is blank, don't use this particular line in the where clause?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Saqib Khan
Saqib Khan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Atlanta_Mike
Atlanta_Mike

The trick is in your join.


SELECT *
FROM a_gatetxn
LEFT JOIN a_yardcheck
ON a_gatetxn.trailerid = a_yardcheck.trailerid
WHERE a_yardcheck.trailerid IS NULL
where
case when a_gatetxn.trailerid is null then a_yardcheck.trailerid
        when a_gatetxn.trailerid='' then a_yardcheck.trailerid
        else a_gatetxn.trailerid end =  a_yardcheck.trailerid
Avatar of Lowfatspread
SELECT *
FROM a_gatetxn
LEFT JOIN a_yardcheck
ON a_gatetxn.trailerid = a_yardcheck.trailerid
--WHERE a_yardcheck.trailerid IS NULL

i'm not clear if you actually need the is null check in Mike's example...


WHERE a_gatetxn.trailerid =
CASE a_gatetxn.trailerid WHEN null THEN a_gatetxn.trailerid
WHEN "" THEN a_gatetxn.trailerid
ELSE a_yardcheck.trailerid END
If he is trying to find those that don't have a match then he does. It's kinf of hard to tell if that's what he isa after.
Ummm... not only is it possible to use a case statement ... but it is done. It's just not needed in this example?