Where Clause with Case statement needed in my query?

I have to fields - may contain combinations of "AWP" or "ACQ" values - the instructions I were given are:
"If both values are AWP, filter should INCLUDE any row where AWP amount > 0.  If both values are ACQ, filter should INCLUDE any row where ACQ amount > 0.  If one values is AWP and the other is ACQ, filter should INCLUDE any row where AWP amount and ACQ amount > 0.  "

Question is - how to I need to construct my WHERE clause to accomodate?  I assume I need some kind of case statement around all of this
Who is Participating?
ErnariashConnect With a Mentor Commented:
Why could you not use simple where like this?
Can your two fields be values others than 'ACQ' and 'AWP'? then you do not need the " IN ('ACQ', 'AWP')"

select * from _TABLE
WHERE ( field1 = 'AWP' AND field2 = 'AWP' AND AWP_Amount > 0 )
OR ( field1 = 'ACQ' AND field2 = 'ACQ'  AND ACQ_amount > 0 ) 
OR (field1 IN ('ACQ', 'AWP') AND field2 IN ('ACQ', 'AWP') AND  field1 <> field2 AND   AWPamount > 0  AND ACQamount > 0 )

Open in new window


How about

WHERE 1 = CASE WHEN field1 = 'AWP' AND field2 = 'AWP' AND AWPamount > 0 THEN 1
                            WHEN field1 = 'ACQ AND field2 = 'ACQ'  AND ACQamount > 0 THEN 1
                            WHEN AWPamount > 0  AND ACQamount > 0 THEN 1
                            ELSE 0

tbaseflugAuthor Commented:
The values may be 'ACQ' or 'AWP' or null
SharathData EngineerCommented:
Eventhough those are NULLs, it won't be a problem. try a Ernariash suggested, you will get what you want.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.