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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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...
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
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?
SELECT *
FROM a_gatetxn
LEFT JOIN a_yardcheck
ON a_gatetxn.trailerid = a_yardcheck.trailerid
WHERE a_yardcheck.trailerid IS NULL