December 10, 2008 at 12:47 pm
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.
December 10, 2008 at 1:25 pm
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
December 10, 2008 at 1:33 pm
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.
December 10, 2008 at 1:53 pm
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
December 10, 2008 at 2:04 pm
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
December 10, 2008 at 2:09 pm
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
December 10, 2008 at 2:18 pm
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.
December 10, 2008 at 2:21 pm
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
December 10, 2008 at 2:34 pm
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"
December 10, 2008 at 8:58 pm
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
Change is inevitable... Change for the better is not.
December 10, 2008 at 11:43 pm
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
December 11, 2008 at 12:37 am
Instead of case you can write query as
select * from infosys
where Integrity != 0
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply