Convert Access IIf Statement to SQL

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 Access IIf Statement to SQL

Author

Topic

junior6202
Starting Member

45 Posts

Posted - 2014-11-17 : 13:21:10

Hi all,

I'm converting a View from access to Sql and I'm stuck on this IF statement.

IIf([FG_Qtys_1].[CoreQty]=0 Or [FG_Qtys_1].[CoreQty] Is Null,[KitCoreOnHand],[FG_Qtys_1].[CoreQty]);

I know that I could use CASE statement but i keep on getting errors.

My Case Statement:
case when [dbo.FG_Qtys_view].[CoreQty]=0 then dbo.FG_Qtys_Kits.KitCoreOnHand
when [dbo.FG_Qtys_view].[CoreQty] ISNULL then [dbo.FG_Qtys_view].[CoreQty]
end as CoreQty

Any help is appreciated.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 13:35:12

CASE WHEN ISNULL([FG_Qtys_1].[CoreQty], 0) = 0
THEN [KitCoreOnHand]
ELSE [FG_Qtys_1].[CoreQty]
END

Or, all in one line:

ISNULL(NULLIF([FG_Qtys_1].[CoreQty], 0), [KitCoreOnHand])

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-17 : 13:35:27

It's due to dbo.FG_Qtys_view being in square brackets. They each would need to be in their own square brackets, but I removed the square brackets since you aren't using reserved words.

case when FG_Qtys_view.CoreQty=0 then FG_Qtys_Kits.KitCoreOnHand
when FG_Qtys_view.CoreQty IS NULL then FG_Qtys_view.CoreQty
end as CoreQty

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-17 : 13:36:03

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

junior6202
Starting Member

45 Posts

Posted - 2014-11-17 : 16:23:24

Thank you all for your Help.

junior6202
Starting Member

45 Posts

Posted - 2014-11-17 : 16:26:35

I have this other If statement that I have to convert.

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

My case Statement.

(CASE WHEN isnull(dbo.SharedCore_BOMCount.BOMCount , 0) <= 1 THEN 1
WHEN isnull(dbo.SharedCore_FGSoldSum.FGSoldSum , 0) = 0 THEN 1 / dbo.SharedCore_BOMCount.BOMCount
WHEN isnull(dbo.SharedCore_FGSold.FGSold , 0) = 0 THEN 0 ELSE dbo.SharedCore_FGSold.FGSold / dbo.SharedCore_TotalSold.TotalSold END) * 100

can you advice if its good? it works but i just want to make sure.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources