case syntax help

[mollytu]

Hi,

What is wrong with this syntax? I want to name the column in the case for use in the #temp table but doesn't seem to like it.


SELECT 'name',
CASE
WHEN ptno IN (99) THEN SUM (recno) 'a'
WHEN ptno IN (13) THEN SUM (recno) 'b'
END
INTO #temp
from myTable

error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'a'.

Thank you

 

[markros]

You can not define a different column name in the output this way. Are you sure you need to return different names for the column? If not, then

SELECT [name],
CASE     WHEN ptno IN (99) 
         THEN SUM (recno) 
WHEN ptno IN (13) 
          THEN SUM (recno) END as Total
INTO #tempfrom from myTable

 

[mollytu]

Thanks. I changed it around so that it's like:

SELECT 'name',
SUM (CASE WHEN ptno IN (99) THEN recno END) AS 'a',
SUM (CASE WHEN ptno IN (99) THEN recno END) AS 'b'
FROM x
...

that works