DB2 - CASE in WHERE clause

Would you please help me with CASE in WHERE clause in DB2.  I want to check the value of a field, if it matches then in the WHERE clause use 1 FIELD, else use the other FIELD... something like this...

SELECT...
FROM...
WHERE
CASE WHEN (Y.GRADE = 'X')
             THEN A.COLOR = 'GREEN'
             ELSE A.TEMP_COLOR = 'GREEN'

Thank you very much for your help.
ttta83Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:

You can use a CASE clause in a lot of places, but in your description the CASE clause belongs in the SELECT item list, not the WHERE clause.


Kent
ttta83Author Commented:
Kent,

You're right.  my CASE clause is not correct... and I need help on that... I want to check the value of GRADE.  
If GRADE = 'Y' then in the WHERE clause I want:
         WHERE A.COLOR = 'GREEN'
if GRADE NOT = 'Y' then in the WHERE clause I want:
         WHERE A.TEMP_COLOR = 'GREEN'

Sorry for the confusion.

Dave FordSoftware Developer / Database AdministratorCommented:

I don't think you need a CASE at all. I think this would work.

HTH,
DaveSlash

WHERE (GRADE = 'Y'
  and A.COLOR = 'GREEN')
   or (GRADE <> 'Y' 
  and A.TEMP_COLOR = 'GREEN')

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Kent OlsenDBACommented:

The comparison moves out of the CASE clause.  CASE only selects the object (column).

  SELECT * FROM mytable
  WHERE CASE WHEN grade = 'Y' THEN color ELSE temp_color END = GREEN.

If grade = 'Y', color will be tested for GREEN, else temp_color will be tested.


Kent
momi_sabagCommented:
you could do something like

where case grade when 'Y' then color else temp_color end = 'GREEN'

but i'm not sure it will perform very well...
apaclarkCommented:
I should be something like:
SELECT...
FROM...
WHERE
CASE WHEN Y.GRADE = 'X'
             THEN A.COLOR
             ELSE A.TEMP_COLOR end = 'GREEN'
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.