Click here to Skip to main content
13,553,262 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi All
I am facing a issue in applying a conditional 'if' in the where clause of the SQL query.
Here is the query. I have removed the unnecessary joins, columns names and multiple conditions in WHERE clause to make it readable.
SELECT TransactionId, TransactionDate , ProcessingDate FROM TransactionDetails
WHERE TransactionId in (2,3,9) AND ...AND ..

Now, I need to select the records from the TransactionDetails table with another transaction Ids 14 & 15 ONLY if the TransactionDate <> ProcessingDate for that particualr record.

What I tried:

Tried using IF ELSE - but it worked only with the variables. I want to apply the condition on the Dates columns from the table.

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)


I get two errors as -
Incorrect syntax near the keyword 'IF'.
Incorrect syntax near 'TransactionId'.

Please comment if you want me to improve my question
Posted 4-Nov-12 20:28pm
Updated 22-Mar-18 5:37am
v2
Comments
User-10860474 1-Feb-18 5:13am
   
And how to handle the condition where i only have "If" condition and not have any "else". i.e. I need to filter the records only when it is not null.

select * from transactionDetails
WHERE OrderID in (400376, 400379)
AND
IF TransactionDate IS NOT NULL
TransactionId in (2,3,9,14,15)

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Try:
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)))
  Permalink  
Comments
User-3642095 5-Nov-12 6:17am
   
It worked .. Thank you OG ..+5.. I wish I could have voted +50.. You saved me ..I have been scratching my head on this for a while...
OriginalGriff 5-Nov-12 6:59am
   
Um...SA? :laugh:
User-3642095 5-Nov-12 7:11am
   
Sorry ..My mistake.. It was a typo.. Confused between the two great names on CP :)
losmac 8-Nov-12 14:52pm
   
+5!
User-10223739 30-Mar-16 6:03am
   
Thanks, Useful for me also.....
User-12729135 27-Sep-16 14:48pm
   
Perfect!
It worked for me ;)
User-13709194 22-Mar-18 11:38am
   
In either case I am getting the same result.. Here is my where clause

WHERE C.ID= @ID
AND ((@istest =1 AND c.name IN ('paul', 'reed','frank', 'cad'))
OR (@istest =0 AND c.name IN IN ('paul', 'reed','cad')))
RedDK 22-Mar-18 15:05pm
   
Perhaps I'm the one who is confused here ... but you might try posting the question as a question in QA. As it stands now, this a comment to which noone will respond until it gets put in the proper place.

"QA" ... that's "Quick Answers" ...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web03 | 2.8.180515.1 | Last Updated 22 Mar 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100