case statements in a where clause

Question

Post reply

case statements in a where clause

Alex Thomas

Ten Centuries

Points: 1120
More actions
June 29, 2005 at 9:08 am

#89634

hi guys,

ive been stuck here before but im looking to include a case statement from within a where clause..

SELECT DISTINCT
e.x,e.y
FROM
events e
INNER JOIN Arrangements a ON
e.arrangement_number = a.number_sql
WHERE CASE
a.type WHEN IN (1,10) THEN e.status IN (3,4) ELSE e.status = 5 END

something like this.. any suggestions?

thanks!
ALex

Viewing 15 posts - 1 through 15 (of 17 total)

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

Vasc SSCarpal Tunnel Points: 4643 More actions · Answer 1

WHERE (a.type IN (1,10) AND e.status IN (3,4)) or (a.type NOT IN (1,10) AND e.status = 5 )

or

CASE

WHEN a.type IN (1,10)

THEN

CASE WHEN e.status IN (3,4) THEN 1

ELSE 0

END

ELSE

CASE WHEN e.status=5 THEN 1

ELSE 0

END

END=1

Kindest Regards,

Vasc

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Just personal opinion.. I find it easy to miss if you put the =1 at the end. I like this syntaxe better (maybe a few c++ guys will agree on this one) :

Where 1 = Case ...... end

Can't miss it if it's in the front.

Alex Thomas Ten Centuries Points: 1120 More actions · Answer 3

cheers guys, the second one worked a treat.

and remi- almost 3000 posts...! good work!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Tx, you got almost 25 post/month yourself... Far more than the pace I was on when I joined.

Maybe you have more answers than I had... or just more questions .

ron k SSCertifiable Points: 6103 More actions · Answer 5

ron k

SSCertifiable

Points: 6103

June 29, 2005 at 11:09 am

#569452

Even this old COBOL programmer agrees with you

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Man and I thaught you were young, that makes you jump up 25 years . The new programmers just didn't face up with this problem (in any C language) :

While (SomeVar = 1)

For those of you who never had to deal with this, it causes and infinite look because SomeVar = 1 actually assigns 1 to the var, and since 1 always evaluate to true, the loop never exits. Those bugs are very hard to track.

The workaround to this problem is to invert the values :

while (1 = Somevar) >> this fails because you can't assign a value to 1, then when the compiler throws the error, you simply correct with (1 == SomeVar).

This is a nice best pratice in C, but it can be very usefull in sql server too sometimes.

ron k SSCertifiable Points: 6103 More actions · Answer 7

As long as you're explaining things maybe you also need to explain what COBOL is?

Farrell Keough SSCoach Points: 17414 More actions · Answer 8

COBOL is a "Business Language" (har har) that old dudes used.

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Sorry but I'm too yound to know about COBOL, I barely know how to spell it. My knowledge came from an interview question for a c++ job. They asked to write a sample coding, and if the guy was using 1 == Something, then you knew he has some good experience in C .