CASE Problem

hello!

Anyone can help me with my query please?

This is supposed to be my query and it has a right ouput.

Select * from itemr where item_type IN ('LS','LI','II')

BUt then, since I cant assign a contant string (LS,LI,II) to conditions,
I have to simply make a sub-query first and return the strings

Select * from item where item_type IN ( Select
case
when ls='Y' then 'LS'
END ,
case
when li='Y' then 'LI'
end,
case
when ii='Y' then 'II'
END
from user_master where user_id='use01')

An error occurs:

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can u help me refine this query such that the sub-query can return a values like ('LS','LI','II') like in my first query?

I tried EXISTS but it only supports one field to return. But I need to return all the fields of item table.

Thanks in advance

BernieWhat the error is try to explain is that you are getting multiple columns in the "in" clause... The "in" can only search a list of values or 1 column,
change your sub-query(the bit in the "in") to use a single column to keep the "in", or expand your where clause to test each case with a seperate "in" using "OR" logic ie

Select *
From Table
Where ( ( A In (<case statement1 >) )
OR ( A In (<case statement2 >) )
OR ( A In (<case statement3 >) ) )

If you have the time i would also recomend that you try and loose the habit of using an "in" statement, as when result sets get big, you will be loading all the values into the stack, this can be nasty for bigger systems, explore using joins if you have more than 5 cases or big tables.

Hope this helps...

Originally posted by bvmantos
hello!

Anyone can help me with my query please?

This is supposed to be my query and it has a right ouput.

Select * from itemr where item_type IN ('LS','LI','II')

BUt then, since I cant assign a contant string (LS,LI,II) to conditions,
I have to simply make a sub-query first and return the strings

Select * from item where item_type IN ( Select
case
when ls='Y' then 'LS'
END ,
case
when li='Y' then 'LI'
end,
case
when ii='Y' then 'II'
END
from user_master where user_id='use01')

An error occurs:

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can u help me refine this query such that the sub-query can return a values like ('LS','LI','II') like in my first query?

I tried EXISTS but it only supports one field to return. But I need to return all the fields of item table.

Thanks in advance

Bernie|||Yet another example of using criteria when a join is more appropriate.

Select *
from item
inner join
(Select case when ls='Y' then 'LS' END LSCase,
case when li='Y' then 'LI' end LICase,
case when ii='Y' then 'II' END IICase
from user_master
where user_id='use01') CaseStatus
on item.item_type = CaseStatus.LSCASE
or item.item_type = CaseStatus.LICASE
or item.item_type = CaseStatus.IICASE

This is still a goofy looking query. I have to wonder whether you should tweak your table schema a bit.

blindman|||Originally posted by bvmantos
hello!

Anyone can help me with my query please?

This is supposed to be my query and it has a right ouput.

Select * from itemr where item_type IN ('LS','LI','II')

BUt then, since I cant assign a contant string (LS,LI,II) to conditions,
I have to simply make a sub-query first and return the strings

Select * from item where item_type IN ( Select
case
when ls='Y' then 'LS'
END ,
case
when li='Y' then 'LI'
end,
case
when ii='Y' then 'II'
END
from user_master where user_id='use01')

An error occurs:

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can u help me refine this query such that the sub-query can return a values like ('LS','LI','II') like in my first query?

I tried EXISTS but it only supports one field to return. But I need to return all the fields of item table.

Thanks in advance

Bernie

What about this idea (it returns nothing but without errors)?

use northwind
go
select *
from Categories
where left(categoryname,1) in
(select
case
when categoryname like 'C%' then 'Y'
when categoryname like 'D%' then 'N'
when categoryname like 'M%' then '0'
else '*'
end
from Categories)|||A straightforward modification to the query you gave is

Select *
from item
where item_type IN
( Select case when ls='Y' then 'LS' END from user_master where user_id='use01'
union all
Select case when li='Y' then 'LI' end from user_master where user_id='use01'
union all
Select case when ii='Y' then 'II' END from user_master where user_id='use01'
)

This should work.
However, there might be other ways to construct a different better query that meets your requirements.

Originally posted by bvmantos
hello!

Anyone can help me with my query please?

This is supposed to be my query and it has a right ouput.

Select * from itemr where item_type IN ('LS','LI','II')

BUt then, since I cant assign a contant string (LS,LI,II) to conditions,
I have to simply make a sub-query first and return the strings

Select * from item where item_type IN ( Select
case
when ls='Y' then 'LS'
END ,
case
when li='Y' then 'LI'
end,
case
when ii='Y' then 'II'
END
from user_master where user_id='use01')

An error occurs:

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can u help me refine this query such that the sub-query can return a values like ('LS','LI','II') like in my first query?

I tried EXISTS but it only supports one field to return. But I need to return all the fields of item table.

Thanks in advance

Bernie|||Select * from item where item_type IN ( Select
(Case
when ls='Y' then 'LS'
when li='Y' then 'LI'
when ii='Y' then 'II'
End)
from user_master where user_id='use01')|||This query has an effect that each row will return at most one value due to the nature of "case when ...". Strictly speaking, this doesn't match what was stated in the requirements.

Originally posted by Jelly Link
Select * from item where item_type IN ( Select
(Case
when ls='Y' then 'LS'
when li='Y' then 'LI'
when ii='Y' then 'II'
End)
from user_master where user_id='use01')|||Ups... I think one item has only one item_type :p|||Originally posted by Jelly Link
Ups... I think one item has only one item_type :p :)|||Select * from item where item_type IN ( Select
(Case
when ls='Y' then
BEGIN
'LS'
END
when li='Y' then
BEGIN
'LI'
END
when ii='Y' then
BEGIN
'II'
End)
from user_master where user_id='use01')