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

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

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

- Advertisement -