CODEWITHSUNDEEP
sqlsql-serverwhere-clause
Joe
Joe

Reputation: 115

Conditional WHERE clause using IN

I have a sql query where I should have something like

select  * from transactionDetails
WHERE OrderID in (400376, 400379)
AND
    IF TransactionDate <> ProcessingDate
        TransactionId in (2,3,9,14,15)
    ELSE
        TransactionId in (2,3,9)

But this gives me a error at IF and TransactionId.

Then I tried

select  * from transactionDetails
WHERE OrderID in (400376, 400379)
AND
    ((TransactionDate <> ProcessingDate AND TransactionId in (2,3,9,14,15))
    OR
  (TransactionDate = ProcessingDate AND TransactionId in (2,3,9)))

But this gives me same result for both <> and = conditions Can someone tell me what I am doing wrong?

Thanks

Upvotes: 1

Views: 78

Answers (4)

Serkan Arslan
Serkan Arslan

Reputation: 13393

As an alternative solution you can use this.

select  * from transactionDetails
WHERE 
    OrderID in (400376, 400379)
    AND ( TransactionId in (2,3,9)
            OR ( TransactionDate <> ProcessingDate AND TransactionId IN (14,15) ) )

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

It is impossible for both of those to return the same. <> and = are mutually exclusive. I suspect a problem with your testing or understanding. Well if one of the dates is null then they would both return false. No way they can both return true. 

(
    (TransactionDate <> ProcessingDate AND TransactionId in (2, 3, 9, 14, 15))
    OR
    (TransactionDate =  ProcessingDate AND TransactionId in (2, 3, 9))
)

I will go out on limb here and assert 

where TransactionDate <> ProcessingDate 
  and TransactionDate =  ProcessingDate

Will return zero rows every time

Upvotes: 1

isaace
isaace

Reputation: 3429

In addition to the above answers, you might want to try using UNION ALL instead of OR and check which one is faster:

SELECT  * FROM transactionDetails
WHERE OrderID in (400376, 400379)
AND TransactionDate <> ProcessingDate 
AND TransactionId in (2,3,9,14,15)
UNION ALL
SELECT  * FROM transactionDetails
WHERE OrderID in (400376, 400379)
AND TransactionDate <> ProcessingDate 
AND TransactionDate = ProcessingDate 
AND TransactionId in (2,3,9)

Upvotes: 0

Krismorte
Krismorte

Reputation: 632

Just organize better the parentheses like a math sentence

select  * from transactionDetails
WHERE OrderID in (400376, 400379)
AND`enter code here`
(
    (TransactionDate <> ProcessingDate AND TransactionId in (2,3,9,14,15))
    OR
  (TransactionDate = ProcessingDate AND TransactionId in (2,3,9))
)

Upvotes: 0

Related Questions