Advice needed implementing CASE within INSERT SELECT outer Join Query

[abraxas]

Hello,
Having recently given up using cursors for more thoughout queries I have recently encountered a problem. Due to a legacy table requiring "Yes"/"No" values instead of Bit 0/1 I need a CASE statement for a joined Insert Select query.
Here is a cut down
Insert Into products
(
prodkey,
special,
intprice
)
Select
p.prodkey,
Case p.special
When 1 Then 'Yes'
When 0 Then 'No'
End,
p.intprice
From
products2 as p
left outer join
products as pp
on p.apn = pp.apn
where
pp.apn is null

"This returns select list for insert... contains fewer items than insert list"

I've checked the list and the number of entries in insert list and select list are equal. Is there something wrong with my CASE syntax? I tried
@Special = Case p.Special but that returns an error too.

My update joins with case work perfectly but I can't seem to get the hang of returning a CASE scalar to the Select list.

Thank you very much

 

[bborissov]

What happen if p.special = NULL?
Try:

Insert Into products
(prodkey,special,intprice)
Select p.prodkey,
Case WHEN p.special = 1 then
          'Yes'
     ELSE 'No' End,
p.intprice
From products2 as p
left outer join products as pp on p.apn = pp.apn
where pp.apn is null

Borislav Borissov

 

[abraxas]

Really sorry folks, I was missing a comma off my select list. It has fifty entries and I'm pretty tired so I just didn't see it. Thank you anyway bborissov.

Guess I can sleep now.