Conditional where in SQL based on Value?

Question

I am doing a report whereby I can't construct the SQL programatically. I have two values that are fed into the report. The values that can be one of the following three options:

redeem
purchase
redeem AND purchase

The query needs to have a WHERE clause. If "redeem" is fed in, it must have:

... WHERE balance < 0

If "purchase" is fed in, it must have:

... WHERE balance >= 0

if both are fed in, this condition can be left out completely, or it can be said:

... WHERE balance >= 0 OR balance < 0 --> but this is redundant

Is there a way to apply this kind of logic in SQL? Is something like this possible in SQL:

SELECT * FROM account WHERE (if param1 = 'redeem' then 'balance <= 0)  ... etc

?

USe case in where, this might be useful: stackoverflow.com/questions/6812276/… — Ghazanfar Mir
– Ghazanfar Mir, Commented May 30, 2013 at 12:55

LoztInSpace · Accepted Answer · 2013-05-30 12:56:18Z

3

Yep. You're almost there.

WHERE (param='redeem' and balance <=0) or (param='purchase' and balance>=0) or (param='redeem AND purchase ')

answered May 30, 2013 at 12:56

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sgeddes Over a year ago

+1 This is the only correct answer I believe -- and here you go (sqlfiddle.com/#!2/865d1/1) (although you can probably remove the last check)

rockstardev Over a year ago

Just noe that the param = 'redeem AND purchase' part is not correct. I used: param1 = "redeem" AND param2 = "purchase" etc

eternay · Accepted Answer · 2013-05-30 14:52:19Z

1

Use CASE statements in your WHERE clause. You can find examples here.

I tried the following code in MySQL and it works:

SET @param = 'purchase';

SELECT * FROM TEST.ACCOUNT
WHERE CASE
    WHEN @param = 'redeem' THEN BALANCE < 0
    WHEN @param = 'purchase' THEN BALANCE >= 0
    ELSE TRUE
END;

edited May 30, 2013 at 14:52

answered May 30, 2013 at 12:55

eternay

3,8142 gold badges32 silver badges28 bronze badges

3 Comments

Kermit Over a year ago

-1 You need to post the relevant code here in case the link goes dead.

sgeddes Over a year ago

For this specific case, I do not believe you can use CASE statements (easily). Please show how if I'm mistaken.

eternay Over a year ago

I've added code that should work... Please tell me if I'm wrong.

Friederike · Accepted Answer · 2013-05-30 12:54:46Z

0

Would this do the trick?

WHERE 
   (purchase is not null && balance >= 0)
OR
   (redeem is not null && balance < 0)

answered May 30, 2013 at 12:54

Friederike

1,28216 silver badges30 bronze badges

Comments

PSR · Accepted Answer · 2013-05-30 12:56:37Z

0

CASE WHEN param1 = 'redeem' THEN balance <= 0

answered May 30, 2013 at 12:56

PSR

40.4k43 gold badges115 silver badges154 bronze badges

1 Comment

Vivek Sadh Over a year ago

why there is a quote before balance ?

Collectives™ on Stack Overflow

Conditional where in SQL based on Value?

4 Answers 4

2 Comments

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related