Case in Where clause

Question

Case in Where clause

Jones'e

Right there with Babe

Points: 774
More actions
October 26, 2007 at 6:52 am

#376537

Is there away to use a Case statement in a where clause based off a value of a local variable? Or what would be the best way to accomplish this.
Example:
WHERE jomast.ftype = 'I' AND jomast.fitype <> '2' and jomast.fstatus = 'RELEASED'
Case @Prodcode
Where ‘01’ thenand jomast.fprodcl Like @Prodcode
Where ’04’ thenor (jomast.ftype = 'I' AND jomast.fitype <> '2' and jomast.fstatus = 'RELEASED'and jomast.fprodcl Like @Prodcode1)
Where ‘10’ thenor (jomast.ftype = 'I' AND jomast.fitype <> '2' and jomast.fstatus = 'RELEASED'and jomast.fprodcl Like @Prodcode2)
End

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Your logic seems flawed? You are testing each condition with the same three fields having the same values, and then you compare the fourth to the variable's value ..

WHERE

jomast.ftype = 'I'

AND jomast.fitype <> '2'

AND jomast.fstatus = 'RELEASED'

So the question is what are you trying to accomplish?

Does the fstatus field need to contain '01', '04', etc... or EQUAL that value.

- If it needs to contain, then you need to use wildcards with LIKE ... '%01%'

- If EQUAL to the value then use "="

- but for multiple values, you'll need to use the IN ....

So it almost seems like you want ...

WHERE

jomast.ftype = 'I'

AND jomast.fitype <> '2'

AND jomast.fstatus = 'RELEASED'

AND jomast.fprodcl IN ('01','04','10')

OR

WHERE

jomast.ftype = 'I'

AND jomast.fitype <> '2'

AND jomast.fstatus = 'RELEASED'

AND (jomast.fprodcl LIKE '%01%'

OR jomast.fprodcl LIKE '%04%'

OR jomast.fprodcl LIKE '%10%')

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jones'e Right there with Babe Points: 774 More actions · Answer 2

Jason thanks for your reply. I realize your concern, but the real point is that I want to change the where clause with the case statement according to the value of the variable @prodcode. I just copied and paste some stuff from want I do now which is comment out some lines when I need to change some results.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

Well, in that CASE. No pun intended.... Instead of using a CASE statement, wrap your logic in parentheses.

WHERE

(jomast.ftype = 'I' AND jomast.fitype <> '2' AND jomast.fstatus = 'RELEASED' AND @prodCode = '01')

OR

(jomast.ftype = 'A' AND jomast.fitype = '2' AND jomast.fstatus = 'RELEASED' AND @prodCode = '09')

OR

(jomast.ftype = 'B' AND jomast.fitype <> '6' AND jomast.fstatus = 'RELEASED' AND @prodCode = '05')

OR

(jomast.ftype = 'X' AND jomast.fitype = '6' AND jomast.fstatus = 'RELEASED' AND @prodCode = '10')

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

you CAN use a case statement in your where clause, but not in the way you're looking at it.

The CASE statement can only return a scalar value, not parts of the WHERE clause.

Something like this is valid however

...

AND jomast.fprodcl like CASE @ProdCode when '01' then @prodcode

when '04' then @prodcode1

when '10' then @prodcode2

end

AND

...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 5

Matt Miller (10/26/2007)
you CAN use a case statement in your where clause, but not in the way you're looking at it.

Matt, I wasn't saying that you COULDN'T use a CASE, just that you COULD use OR's .... 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

I know Jason - I was just answering OP's question directly...

I need to clarify who I'm speaking to:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Antares686 SSC Guru Points: 125444 More actions · Answer 7

To me this looks like what you are saying you are after

WHERE

jomast.ftype = 'I'

AND jomast.fitype <> '2'

AND jomast.fstatus = 'RELEASED'

AND jomast.fprodcl Like (CASE @Prodcode

WHEN '01' THEN @Prodcode

WHEN '04' THEN @Prodcode1

Where '10' THEN @Prodcode2)

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Sorry had a typo and the edit button won't work

WHERE

jomast.ftype = 'I'

AND jomast.fitype <> '2'

AND jomast.fstatus = 'RELEASED'

AND jomast.fprodcl LIKE (CASE @Prodcode

WHEN '01' THEN @Prodcode

WHEN '04' THEN @Prodcode1

WHEN '10' THEN @Prodcode2)

Antares686 SSC Guru Points: 125444 More actions · Answer 9

Darn it, forogt my end

WHERE

jomast.ftype = 'I'

AND jomast.fitype <> '2'

AND jomast.fstatus = 'RELEASED'

AND jomast.fprodcl LIKE (CASE @Prodcode

WHEN '01' THEN @Prodcode

WHEN '04' THEN @Prodcode1

WHEN '10' THEN @Prodcode2

END)

Jones'e Right there with Babe Points: 774 More actions · Answer 10

Thanks for your help. I think I got want I needed now. Thanks!