Results 1 to 10 of 10

Thread: CASE Problem

  1. #1
    Join Date
    Oct 2003
    Location
    Manila, Philippines
    Posts
    20

    Unanswered: 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


    Bernie

  2. #2
    Join Date
    Oct 2003
    Location
    UK
    Posts
    4

    Re: CASE Problem

    What 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

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: CASE Problem

    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)

  5. #5
    Join Date
    Nov 2003
    Posts
    48

    Re: CASE Problem

    A straightforward modification to the query you gave is

    Code:
    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
    Shianmiin

  6. #6
    Join Date
    Dec 2003
    Posts
    39
    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')

  7. #7
    Join Date
    Nov 2003
    Posts
    48
    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')
    Shianmiin

  8. #8
    Join Date
    Dec 2003
    Posts
    39
    Ups... I think one item has only one item_type

  9. #9
    Join Date
    Nov 2003
    Posts
    48
    Originally posted by Jelly Link
    Ups... I think one item has only one item_type
    Shianmiin

  10. #10
    Join Date
    Mar 2002
    Posts
    192

    Try this one.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •