How to use case in where clause

Question

Post reply

How to use case in where clause

halifaxdal

SSCoach

Points: 19741
More actions
December 10, 2008 at 12:47 pm

#383551

Hi guys,
Here is my task:
I have couple records marked as integrity = 1 or 2 or 3 or 4
I need to write a query to pull out all records that has integrity = 1 or 2 or 3 or 4 but just exclude those integrity = 0
Now this will be done through an external parameter input: if it is "all classified", then pull out those classified records for me, otherwise, give me those that meet the parameter.
It would be something like this:
declare @integrity varchar(20)
set @integrity = 'all classified'
select * from infosys
where integrity = case when @integrity = 'all classified' then 1 or 2 or 3 or 4 else 0 end
however, only this works:
where integrity = case when @integrity = 'all classified' then 1 else 0 end
Can anyone give me any hints? I don't want to build a dynamic query.
Thanks.

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

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

gbd77rc Ten Centuries Points: 1031 More actions · Answer 1

Hi,

You cannot specify the OR operator in a CASE statement, but you can do this.

SELECT *

FROM sysinfo

WHERE integrity = CASE WHEN @integrity = 'all classified' THEN 1 ELSE 0 END

OR integrity = CASE WHEN @integrity = 'all classified' THEN 2 ELSE 0 END

OR integrity = CASE WHEN @integrity = 'all classified' THEN 3 ELSE 0 END

OR integrity = CASE WHEN @integrity = 'all classified' THEN 4 ELSE 0 END

It is not nice and I would actually do something like this.

IF @integrity = 'all classified'

SELECT * FROM sysinfo WHERE integrity BETWEEN 1 AND 4

ELSE

SELECT * FROM sysinfo WHERE integrity = 0

Regards

Richard...

http://www.linkedin.com/in/gbd77rc

halifaxdal SSCoach Points: 19741 More actions · Answer 2

Thank you for your reply, the reason why I don't want to use (if possible) the if else is: I have not only integrity be the criteria, but several others, so making a combination really seems to create lots more work.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

I deleted my earlier query because I forgot to think outside the box. Can you work with this?

declare @integrityTbl table (integrity int primary key)

if @integrity = 'all classified'

begin

insert into @integrity

select 1 union all

select 2 union all

select 3 union all

select 4

end

else

begin

insert into @integrity

select 0

end

Then in your main query, JOIN @integrityTbl IT on infosys.integrity = IT.integrity

You would have to declare and populate separate table variables for other parameters and join them as well. Yes, it is a lot of work, but SQL doesn't really do what you are wishing for it to do. At least this approach might give you better performance.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

halifaxdal SSCoach Points: 19741 More actions · Answer 4

Hi Bob,

Thank you for the input, you are really making things even complex, although you won't say that. (don't panic, I know you are in good intention ;))

If I have to choose, I might choose to write something similar to Richard's idea. Easy to understand, right? especially when more criteria added up, currently I have four(other than Integrity) as input parameters.

But this is really not a good idea, good idea should be something like in my original post:

select * from infosys

where integrity = case when @integrity = 'all classified' then 1 or 2 or 3 or 4 else 0 end

or

where when @integrity = 'all classified' then integrity = 1 or integrity = 2 or integrity = 3 or integrity = 4 else integrity = 0 end

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

My intention was to suggest something that would perform well, since simple is going to run poorly.

Here is the original suggestion from my first post.

It is simple and easy to read.

It will do a scan and run poorly.

Good luck.

WHERE (@integrity = 'all classified' and integrity between 1 and 4)

or (@integrity <> 'all classified' and integrity = 0)

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

halifaxdal SSCoach Points: 19741 More actions · Answer 6

Hey Bob,

You know what? your original ( :hehe:, yes, the one you once deleted) suggestion is better for me in this case, performance is not an issue at all.

Thank you.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

You're welcome. I just want to make it clear that I don't recommend it in any situation where performance is, or may become, an issue. That's why I deleted it originally.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

Of course you can have OR and AND in the CASE contructor!

declare @integrity varchar(20)

set @integrity = 'all classified'

select*

frominfosys

wherecase

when @integrity = 'all classified' and integrity between 1 and 4 THEN 1

when @integrity = integrity THEN 1

ELSE 0

END = 1

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

halifaxdal (12/10/2008)
Hi guys,
Here is my task:
I have couple records marked as integrity = 1 or 2 or 3 or 4
I need to write a query to pull out all records that has integrity = 1 or 2 or 3 or 4 but just exclude those integrity = 0
Now this will be done through an external parameter input: if it is "all classified", then pull out those classified records for me, otherwise, give me those that meet the parameter.
It would be something like this:
declare @integrity varchar(20)
set @integrity = 'all classified'
select * from infosys
where integrity = case when @integrity = 'all classified' then 1 or 2 or 3 or 4 else 0 end
however, only this works:
where integrity = case when @integrity = 'all classified' then 1 else 0 end
Can anyone give me any hints? I don't want to build a dynamic query.
Thanks.

Impatient, aren't we? 😉 It's a forum, not a help button :hehe:

--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)

johncyriac SSCommitted Points: 1542 More actions · Answer 10

any way try this also

declare @integrity varchar(20)

set @integrity = 'all classified'

select * from infosys

where integrity like case when @integrity = 'all classified' then '[1234]' else '0' end

like will work with integers too

regards

john

sar_kan25 SSC-Addicted Points: 460 More actions · Answer 11

Instead of case you can write query as

select * from infosys

where Integrity != 0