Solved

# Using CASE in a where clause

Posted on 2006-06-02
226 Views
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
0
Question by:ONYX

LVL 23

Accepted Solution

You can not use Case statement with Where Clause.
0

LVL 13

Expert Comment

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
0

LVL 28

Expert Comment

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
0

LVL 50

Expert Comment

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

0

LVL 4

Expert Comment

WHERE a_gatetxn.trailerid =
CASE a_gatetxn.trailerid WHEN null THEN a_gatetxn.trailerid
WHEN "" THEN a_gatetxn.trailerid
ELSE a_yardcheck.trailerid END
0

LVL 13

Expert Comment

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

LVL 13

Expert Comment

Ummm... not only is it possible to use a case statement ... but it is done. It's just not needed in this example?
0

## Featured Post

This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.