Using IIF statements in Update Query

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 2000 Forums

Transact-SQL (2000)

Using IIF statements in Update Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-25 : 11:44:06

Roger writes "Hello,

I'm converting a Microsoft Access 2000 database to SQL Server, as the data is just becoming too large.

Anyway, during the conversion, I tried to convert this Access query:

UPDATE T_rptNavision
SET T_rptNavision.SnackType = IIf(Len([SKU])>4,IIf(MID([SKU],2,1)=4,"Bun",IIf(MID([SKU],2,1)=5,"Snack",IIf(MID([SKU],2,1)=6,"Snack","Pie"))));

I tried converting it to SQL Server:

CREATE PROCEDURE Q_002_Extract_Sales_Monthly_Sales_SellTo
AS UPDATE T_rptNavision
SET T_rptNavision.SnackType = IIf(Len([SKU])>4,IIf(SUBSTRING([SKU],2,1)=4,"Bun",IIf(SUBSTRING([SKU],2,1)=5,"Snack",IIf(SUBSTRING([SKU],2,1)=6,"Snack","Pie"))));

This is my conversion - it doesn't work. Would you please help me? It is complaining about the > sign. I don't think it wants to do the IIF statement.

Thank you,

--Roger"

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-04-25 : 12:01:17

iif is specific to Access. Use case expressions

Something like

UPDATE T_rptNavision
SET T_rptNavision.SnackType =
case when len(sku) > 4 then
case substring(sku,2,1)
when '4' then 'Bun'
when '5' then 'Snack'
when '6' then 'Snack'
else 'Pie' end end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources