convert iif statement - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

convert iif statement

Author

Topic

theHydra
Starting Member

42 Posts

Posted - 2011-09-12 : 17:44:24

Does anyone know of an easy way to convert this iif statement from Access into something SQL can handle?

Thanks

IIf([flextend]=-1,(IIf((([fquantity]*[factqty])-[fqty_iss])<0,0,([fquantity]*[factqty])-[fqty_iss]))*-1,(IIf(([factqty]-[fqty_iss])<0,0,([factqty]-[fqty_iss])*-1)))

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-12 : 20:07:13

Hey there,

Try:
[CODE]
select
case when flextend=-1 then
(case when (([fquantity]*[factqty])-[fqty_iss])<0 then 0 else (([fquantity]*[factqty])-[fqty_iss]) end)*-1 else
(case when [factqty]-[fqty_iss]<0 then 0 else ([factqty]-[fqty_iss])*-1 end) end
[/CODE]

theHydra
Starting Member

42 Posts

Posted - 2011-09-12 : 22:44:59

That looks to work, thanks a million flameblaster!

Much appreciated.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-13 : 00:31:11

Sure thing...basic syntax can be found all over the web, but it's very similar to iif:

if you didn't see it from the statement, it's:

case when column="somevalue" then "returnvalue" else "someothervalue" end

or for simple expressions:

Case column when "somevalue" then "returnvalue" else "someothervalue" end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources