access IIf statement to SQL - 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

access IIf statement to SQL

Author

Topic

junior6202
Starting Member

45 Posts

Posted - 2014-12-15 : 11:15:03

Hi All,

I'm converting an IF statement from Access to SQl. I am using CASE statement but I'm having trouble with the ELSE statement where I have to multiply by [CubicInches] and then divide by 33600. Any help will be appreciated.

Access:
(IIf(Val(nz([QtyOnHand],0))>Val(nz([AnticipatedQty],0)),Val(nz([QtyOnHand],0)),Val(nz([AnticipatedQty],0))))*([CubicInches])/33600

My SLQ statement so far:

case when ISNULL(CONVERT(int,[QtyOnHand]),0)>ISNULL(CONVERT(int,[AnticipatedQty]),0) then ISNULL(CONVERT(int,[QtyOnHand]),0) else ISNULL(CONVERT(int,[AnticipatedQty]),0)
end

I am not sure how to addressed the multiplication by [CubicInches] and division by 33600. Thanks in advance.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-12-15 : 12:41:48

[code]case when ISNULL(CONVERT(int,[QtyOnHand]),0)>ISNULL(CONVERT(int,[AnticipatedQty]),0)
then ISNULL(CONVERT(int,[QtyOnHand]),0)
else ISNULL(CONVERT(int,[AnticipatedQty]),0)
end*[CubicInches]/33600[/code]

junior6202
Starting Member

45 Posts

Posted - 2014-12-15 : 16:10:27

Thank for your answer bitsmed. Here is a more complicated one, is a nested IIF statement in Access and I'm completely lost. I can convert each individual IIF statement but trying to put it n a CASE i get lost.

ACCESS:
IIf(((IIf(Val(nz([QtyOnHand],0))>Val(nz([AnticipatedQty],0)),Val(nz([QtyOnHand],0)),Val(nz([AnticipatedQty],0))))*([CubicInches])/33600)>0.55,
((IIf(Val(nz([QtyOnHand],0))>Val(nz([AnticipatedQty],0)),Val(nz([QtyOnHand],0)),Val(nz([AnticipatedQty],0))))*([CubicInches])/55400),
((IIf(Val(nz([QtyOnHand],0))>Val(nz([AnticipatedQty],0)),Val(nz([QtyOnHand],0)),Val(nz([AnticipatedQty],0))))*([CubicInches])/33600))

I appreciaye the help.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-15 : 17:16:10

Work through the simpler one and understand how it works as a case statement, then unravel the more complicated one,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources