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])
Go to Top of Page

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/
Go to Top of Page

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/
Go to Top of Page

junior6202
Starting Member

45 Posts

Posted - 2014-11-17 : 16:23:24
Thank you all for your Help.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -