Use of multiple case conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use of multiple case conditions

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-22 : 07:43:51

Trying to update a temp table using several case lines:

update #TempTable 
       set #TempTable.record_id
         case  
            when #TempTable.record_id = ' ' then  '1'
           else
              #TempTable.record_id
         end,
       set #TempTable.supplier_code
         case  
            when #TempTable.supplier_code = ' ' then  'T1'
           else
              #TempTable.supplier_code
         end,
       set    #TempTable.cred_flag =
         case  
            when #TempTable.qty_net < 0 then  'G'
           else
              #TempTable.cred_flag 
         end

Gives me syntax error near keyword 'case'.

Thank you.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-22 : 07:56:57

[code]update #TempTable
set record_id =
case
when record_id = ' ' then '1'
else
record_id
end,
supplier_code =
case
when supplier_code = ' ' then 'T1'
else
supplier_code
end,
cred_flag =
case
when qty_net < 0 then 'G'
else
cred_flag
end[/code]You only specify the SET keyword once, and must include the equals sign (=) between each column and the value/expression you want it to have.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-04-22 : 08:14:19

Thank you. I forgot one thing:

qty_net is a char(11) and may have a '-' to indicate a negative number. If so I need to set cred_flag = 'G' and then also remove the '-' from the qty_net. Is that possible, someting like:

code]cred_flag =
case
when qty_net like '%-%'then 'G'
else
cred_flag [/code]

I am not sure how to remove the'-' in from the qty_net in the same loop.

Thank you.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-22 : 09:14:06

HOw can qty_net be a char and then used like this
Posted - 04/22/2011 : 07:56:57
--------------------------------------------------------------------------------

update #TempTable
set record_id =
case
when record_id = ' ' then '1'
else
record_id
end,
supplier_code =
case
when supplier_code = ' ' then 'T1'
else
supplier_code
end,
cred_flag =
case
when qty_net < 0 then 'G'
else
cred_flag
end
You should be careful treating strings like numbers
select max(col)
from(select '123456' as col union select '9999') a

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources