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.

In which rdbms?

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

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