Select case Problem

Question

Select case Problem

951

Newbie

Points: 9
More actions
November 12, 2007 at 11:55 pm

#200434

hi ,i used select case statement in my report
when the case eq 1,3,5, & 7, I get the result.
But when the case eq 2,4,6, & 8, there is no result.
If you have any suggestions about this, pls let me know, thanks a lot.
SELECT A.ID, A.TITLE, A.PRICE, A.R3, A.R4,
FROM A LEFT OUTER JOIN B ON A.ID = B.ID
WHERE (@stat = CASE
WHEN R3 <> round(PRICE * 0.53, 2) AND L2 = '1' THEN 1
WHEN R4 <> round(PRICE * 0.41, 2) AND L2 = '1' THEN 2
WHEN R3 <> round(PRICE * 0.60, 2) AND L2 = '2' THEN 3
WHEN R4 <> round(PRICE * 0.56, 2) AND L2 = '2' THEN 4
WHEN R3 <> round(PRICE * 0.76, 2) AND L2 = '3' THEN 5
WHEN R4 <> round(PRICE * 0.79, 2) AND L2 = '3' THEN 6
WHEN R3 <> round(PRICE * 0.75, 2) AND L2 = '4' THEN 7
WHEN R3 <> round(PRICE * 0.75, 2) AND L2 = '4' THEN 8
ELSE 0 END)

Viewing 2 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

I can tell you you'll never get "8" because it has the exact same criteria as "7" which will always qualify first in your case statement. They ARE order sensitive.

Also, just hazarding a guess here... I'll just bet that the data in "A" is such that when the conditions for R4 match, the conditions for the previous R3 also match... that means, again, because they are order sensitive, that the R4's will never qualify because the R3's always qualify first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)