Translate If statement from Access to Tsql

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

Translate If statement from Access to Tsql

Author

Topic

junior6202
Starting Member

45 Posts

Posted - 2014-09-15 : 12:57:20

Hi All,
I was wondering if anyone could give me a hand in translating this if statement from access into TSQL. Thanks in advance.

IIf(([BOMCount])<=1,1,IIf([FGSoldSum])=0,1/[BOMCount],IIf(([FGSold])=0,0,[FGSold]/[TotalSold]))

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-15 : 13:28:04

[code]
CASE
WHEN BOMCount <= 1 THEN 1
ELSE CASE
WHEN FGSoldSum = 0 THEN 1/BOMCount
ELSE CASE
WHEN FGSold = 0 THEN 0
ELSE FGSold / TotalSold
END
END
END
[/code]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-15 : 13:28:19

If you are on SQL 2012 or later, you can use the expression as it is, assuming it correctly parses and runs in MS Access. If you are on an earlier version of SQL Server, use CASE expressions, example like this:

select 

CASE 
	WHEN ([BOMCount])<=1 THEN 1
	WHEN ([FGSoldSum])=0 THEN 1/[BOMCount]
	WHEN ([FGSold]) = 0 tHen 0
	ELSE [FGSold]/[TotalSold]
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-15 : 13:47:54

Also, division behaves differently in Access and SQL Server.
In SQL Server you can suffer with integer division.

CASE
	WHEN BomCount <= 1 THEN 1E
	WHEN FGSoldSum = 0 THEN 1E / BomCount
	WHEN FGSold = 0 THEN 0E
	ELSE 1E * FGSold / TotalSold
END

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

junior6202
Starting Member

45 Posts

Posted - 2014-09-15 : 15:11:31

Thank you all for you help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources