April 28, 2008 at 5:59 am
Can I use IN clause in CASE function.
I have a table.
CREATE TABLE TABLE1 (ID INT IDENTITY(1,1),Reviewed char(1))
INSERT INTO TABLE1 SELECT 'P'
INSERT INTO TABLE1 SELECT 'F'
INSERT INTO TABLE1 SELECT 'F'
INSERT INTO TABLE1 SELECT 'N'
INSERT INTO TABLE1 SELECT 'N'
INSERT INTO TABLE1 SELECT 'P'
INSERT INTO TABLE1 SELECT 'P'
INSERT INTO TABLE1 SELECT 'F'
My query is like this.
select * from table1 CASE reviewed IN WHEN @reviewed = 'P' THEN ('P')
WHEN @reviewed = 'F' THEN ('F')
WHEN @reviewed = 'B' THEN ('P','F')
ELSE reviewed IN ('P','F','N') END
I am getting syntax error.Help me? The reviewed column contains only three type of data. I have placed this query inside a procedure and passing @reviewed value as input parameter. Based on the @reviewed value the reviewed column should be filtered.IF I don't pass any value then all the rows should be selected. How can I do this?
April 28, 2008 at 6:19 am
You could try something like this:
DECLARE @TV table (reviewed char(1))
IF @reviewed IN ('P','B','N') INSERT INTO @TV VALUES ('P')
IF @reviewed IN ('F','B','N') INSERT INTO @TV VALUES ('F')
IF @reviewed = 'N' INSERT INTO @TV VALUES ('N')
SELECT t.* FROM table1 t
JOIN @TV j ON t.reviewed = j.reviewed
Even if I haven't got the logic or the syntax exactly right, I'm sure you get the idea.
John
April 28, 2008 at 8:12 am
Thanks John Mitchell for your reply. But can we use the IN clause in
case statement? I have a similiar case in other table.
CREATE TABLE TABLE1 (ID INT IDENTITY(1,1),PERSON_NAME VARCHAR(50),ReviewedID int)
INSERT INTO TABLE1 SELECT 'Tim',23
INSERT INTO TABLE1 SELECT 'Pun',89
INSERT INTO TABLE1 SELECT 'kar',0
INSERT INTO TABLE1 SELECT 'Abba',76
INSERT INTO TABLE1 SELECT 'Kim',150
INSERT INTO TABLE1 SELECT 'Abba',null
INSERT INTO TABLE1 SELECT 'Jack',null
INSERT INTO TABLE1 SELECT 'David',0
INSERT INTO TABLE1 SELECT 'Thomas',32
INSERT INTO TABLE1 SELECT 'Jack',8
INSERT INTO TABLE1 SELECT 'Thomas',0
this table contains around 10,000 records. I have written this query inside a procedure.It has
two input parameters @sign and @person_name,both are optional.I have to select only top 100 records
from the table.If the @sign is null then all the top 100 records irrespective of any value in reviewed column.
If he passes the person_name then only records matching that record otherwise all top 100 records.
The query is like this.
select TOP 100 * from table1 where CASE reviewedID
WHEN @sign = 'Y' THEN <> 0
WHEN @sign = 'N' THEN = 0
ELSE reviewedID end
and PERSON_NAME LIKE
CASE WHEN @SearchBy ='PATIENT' THEN (replace (@Searchfor,'','''')+'%')
ELSE '%' END
How can this be solved?
April 28, 2008 at 8:29 am
No, you can not use an 'IN' clause in a 'CASE' statement as you are trying to.
You can use an 'IN' clause in the 'WHEN' part of the 'CASE' statement. ex. CASE WHEN @Sign IN ('N', '') THEN ....
As to how to accomplish your second part, try this:
IF @Sign IS NULL OR @Sign IN ('N','')
SELECT TOP 100 *
FROM Table1
WHERE PERSON_NAME = CASE WHEN @Person_Name IS NULL THEN PERSON_NAME ELSE @Person_Name END
ELSE
SELECT TOP 100 *
FROM Table1
WHERE PERSON_NAME = CASE WHEN @Person_Name IS NULL THEN PERSON_NAME ELSE @Person_Name END
AND ReviewedID IS NOT NULL
Dave Novak
April 28, 2008 at 8:32 am
I've just seen another question answered something like this:
select TOP 100 * from table1 where
(@sign = 'Y' AND reviewedID != 0) OR
(@sign = 'N' AND reviewedID = 0) OR
(@sign NOT IN ('Y', N'))
and (PERSON_NAME LIKE
(@SearchBy = 'PATIENT' AND PERSON_NAME LIKE (replace (@Searchfor,'','''')+'%') OR
(@SearchBy != 'PATIENT' AND PERSON NAME LIKE '%')
)
Would that work for you?
John
April 28, 2008 at 10:22 am
Another option to try:
select * from table1
WHERE CASE WHEN @reviewed = 'P' and reviewed = ('P') then 1
WHEN @reviewed = 'F' and reviewed = ('F') then 1
WHEN @reviewed = 'B' and reviewed in ('P','F') then 1
WHEN @reviewed not in ('P','F','B') AND reviewed IN ('P','F','N') THEN 1
ELSE 0 END
= 1
----------------------------------------------------------------------------------
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?
April 28, 2008 at 11:11 am
The problem isn't that you're using In in a Case statement. It's how it was written.
Your original query doesn't have the word "Where" in it. That's the syntax error your got. (To be technical, because of the way it was written, "CASE" becomes an alias for the table name, and then the rest just confuses SQL.)
When you want to limit the rows, you need to use "Where".
select * from table1
Where (the rules you want to follow)
Then the rules have to be written as equations that SQL can test.
select * from table1
where reviewed = 'p'
Would be a valid test, because SQL can look at that and see which ones it works on.
select * from table1
Where (reviewed = 'p' and @reviewed = 'p')
That would be a valid test, since SQL can check both statements.
That brings us to:
select * from table1
Where
(reviewed = 'p' and @reviewed = 'p')
or
(reviewed = 'f' and @reviewed = 'f')
or
(reviewed in ('p', 'f') and @reviewed = 'b')
or
(@reviewed not in ('p','f','b') or @reviewed is null)
(Sorry if I'm overexplaining this. I'm hoping it will help.)
Case can use In, for example:
select
case
when reviewed in ('p','f') then 'Both'
when reviewed = 'p' then 'P'
when reviewed = 'f' then 'F'
end
from table1
It just can't be used quite the way you wanted to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply