SQL Server - IIF statement 1

[niteflite]

I'm traditionally an Access user. I have this much bigger database to work with so am using SQL Server 7.0. The problem I've encountered is with an iif statement. In the access query I would create a field in a query by using this statement:

IIf([adqty]=0,([iaqty]),([iaqty]/[adqty])) AS gt_5_asset

What is wrong with this statement?

The entire query in Access is:

SELECT [dbo_G].[fsc], [dbo_G].[niin], [dbo_G].[itemname], [dbo_G].[com], [dbo_G].[aac], [dbo_G].[ssc], [dbo_G].[amc], [dbo_G].[amsc], [dbo_G].[qfd], [dbo_G].[nsoqty], [dbo_G].[alt], [dbo_G].[plt], [dbo_G].[mauc], [dbo_G].[sup], [dbo_G].[iaqty], [dbo_G].[roqty], [dbo_G].[slqty], [dbo_G].[assumed], [dbo_G].[disposed], [dbo_G].[lbuyd], [dbo_G].[ldemd], [dbo_G].[dq023], [dbo_G].[dq022], [dbo_G].[dq021], [dbo_G].[dq014], [dbo_G].[dq013], [dbo_G].[dq012], [dbo_G].[dq011], [dbo_G].[dq004], [dbo_G].[adqty], [dbo_G].[icc], [dbo_G].[pcm], [dbo_G].[wsec], [dbo_G].[wsic], [dbo_G].[ui], [dbo_G].[boqty], [dbo_G].[uca], [dbo_G].[ucf], [dbo_G].[ucm], [dbo_G].[ucn], [dbo_G].[uco], [dbo_G].[duinqty], [dbo_G].[smcc], [dbo_G].[mig], [dbo_G].[cbu], [dbo_G].[pgc], [dbo_G].[manage], IIf([adqty]=0,([iaqty]),([iaqty]/[adqty])) AS gt_5_asset
FROM dbo_G
WHERE ((([dbo_G].[aac])="t" Or ([dbo_G].[aac])="v" Or ([dbo_G].[aac])="y&quot) Or ((([dbo_G].[ssc])="6&quot) Or ((([dbo_G].[amsc])="y&quot) Or ((([dbo_G].[adqty])=0)) Or (((IIf([adqty]=0,([iaqty]),([iaqty]/[adqty])))>=5));

 

[tlbroadbent]

Use the CASE function in T-SQL.

CASE WHEN [adqty]=0 THEN [iaqty] ELSE [iaqty]/[adqty] END AS gt_5_asset Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.