CODEWITHSUNDEEP
sqlsql-server
Rasmita Dash
Rasmita Dash

Reputation: 937

What's the correct way of using case in where clause

I am getting this error "Incorrect syntax near '<'.". What's the correct way of using case, comparison operator in where clause?

Here is my Sql query:

SELECT COUNT(AL.PKAPPLICATIONID) FROM APPLICATIONLOGO AL 
LEFT JOIN THIRDPARTYSESSIONKEY TSK ON AL.PKAPPLICATIONID = TSK.APPLICATIONSOURCEID WHERE
CASE WHEN TSK.CREATEDON IS  NULL 
THEN TSK.CREATEDON < GETDATE()
ELSE 1=1
END

Upvotes: 0

Views: 89

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

This is your where clause:

WHERE CASE WHEN TSK.CREATEDON IS  NULL THEN TSK.CREATEDON < GETDATE() ELSE 1=1
      END

I assume you mean IS NOT NULL; otherwise the logic doesn't make much sense.

In general, the best way to use case in a where clause is to avoid it. The following is easier to read and probably what you intend:

WHERE TSK.CREATEDON IS NULL OR TSK.CREATEDON < GETDATE()

The original logic for your version is simply:

WHERE TSK.CREATEDON IS NOT NULL

And, the specific answer to your question is that a case statement returns a value and a boolean result is not a value. So, the following does not work:

where (case when c = 'a' then a = b else c = b end)

The following does:

where (case when c = 'a' then a else c end) = b

But as I say, better to just use the basic logical operators in most cases.

Upvotes: 1

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

Are you trying to get all records with CREATEDON date as yesterday and older and should include columns where CREATEDON is NULL. If that's the case, the below query will work

SELECT COUNT(AL.PKAPPLICATIONID)
FROM APPLICATIONLOGO AS AL
    LEFT JOIN THIRDPARTYSESSIONKEY AS TSK
    ON AL.PKAPPLICATIONID = TSK.APPLICATIONSOURCEID
WHERE CAST(ISNULL(TSK.CREATEDON,GETDATE()-1) AS DATE) < CAST(GETDATE() AS DATE)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460228

Use an OR:

SELECT Count(AL.Pkapplicationid) 
FROM   APPLICATIONLOGO AL 
       LEFT JOIN THIRDPARTYSESSIONKEY TSK 
              ON AL.Pkapplicationid = TSK.Applicationsourceid 
WHERE TSK.Createdon IS NULL  
  OR  TSK.Createdon < Getdate()

I assume that this is the actual logic since yours makes not sense:

WHEN TSK.Createdon IS NULL THEN TSK.Createdon < Getdate()

or do you actually want to use a different column in case Createdon IS NULL?

Then you could use COALESCE / ISNULL:

WHERE COALESCE(TSK.Createdon, TSK.OtherColumn) < Getdate()

Upvotes: 3

Related Questions