Results 1 to 4 of 4

Thread: a quick question regarding CASE and SELECT statement

  1. #1
    Join Date
    Mar 2007
    Posts
    1

    a quick question regarding CASE and SELECT statement

    I'm having trouble with using Select statement inside of Case.
    Take a look at the following script:

    -----
    case
    when pl.config_itm2_id is NULL
    then 0
    when pl.config_itm2_id = 0
    then 0
    else
    select c1.ciValue from MTMWSTB01.icSales.dbo.config_itm2 as c1
    where (c1.config_itm2_id = 55)
    and (c1.ciNodeName = 'C2List')
    end
    as c2List
    ------
    I can execute that select statement alone successfully. Once I execute the entire sql script
    I got this error message:
    Server: Msg 156, Level 15, State 1, Line 124
    Incorrect syntax near the keyword 'select'.
    Server: Msg 156, Level 15, State 1, Line 127
    Incorrect syntax near the keyword 'end'.

    Could anybody point out where the problem is? Thanks a lot. Appreciate it.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    In which rdbms?

  3. #3
    Join Date
    Mar 2007
    Posts
    2
    try this

    case
    when pl.config_itm2_id is NULL
    then 0
    when pl.config_itm2_id = 0
    then 0
    else
    (select c1.ciValue from MTMWSTB01.icSales.dbo.config_itm2 as c1
    where (c1.config_itm2_id = 55)
    and (c1.ciNodeName = 'C2List'))
    end
    as c2List

  4. #4
    Join Date
    Mar 2007
    Posts
    2
    Quote Originally Posted by bzhang
    I'm having trouble with using Select statement inside of Case.
    Take a look at the following script:

    -----
    case
    when pl.config_itm2_id is NULL
    then 0
    when pl.config_itm2_id = 0
    then 0
    else
    select c1.ciValue from MTMWSTB01.icSales.dbo.config_itm2 as c1
    where (c1.config_itm2_id = 55)
    and (c1.ciNodeName = 'C2List')
    end
    as c2List
    ------
    I can execute that select statement alone successfully. Once I execute the entire sql script
    I got this error message:
    Server: Msg 156, Level 15, State 1, Line 124
    Incorrect syntax near the keyword 'select'.
    Server: Msg 156, Level 15, State 1, Line 127
    Incorrect syntax near the keyword 'end'.

    Could anybody point out where the problem is? Thanks a lot. Appreciate it.
    Try with the select statement in the paranthesis as below:

    case
    when pl.config_itm2_id is NULL
    then 0
    when pl.config_itm2_id = 0
    then 0
    else
    (select c1.ciValue from MTMWSTB01.icSales.dbo.config_itm2 as c1
    where (c1.config_itm2_id = 55)
    and (c1.ciNodeName = 'C2List'))
    end
    as c2List

Posting Permissions

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