How to apply conditional statement in SQL WHERE clause

Question

5.00/5 (1 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.

SQL

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.

SQL

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

bbirajdar

Updated 22-Mar-18 5:37am

v2

Add a Solution

Comments

Member 10894637 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2012-11-04T21:18:00

Solution 1

Try:

SQL

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

Posted 4-Nov-12 21:18pm

OriginalGriff

Comments

bbirajdar 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:

bbirajdar 5-Nov-12 7:11am

Sorry ..My mistake.. It was a typo.. Confused between the two great names on CP :)

Maciej Los 8-Nov-12 14:52pm

+5!

Umesh AP 30-Mar-16 6:03am

Thanks, Useful for me also.....

Member 12762716 27-Sep-16 14:48pm

Perfect!
It worked for me ;)

Member 13741334 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" ...