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
 Nested Access IIF statement to SQL Case

Author  Topic 

junior6202
Starting Member

45 Posts

Posted - 2014-12-15 : 16:42:02
Can any one help me to convert this nested IIf statment into a CASE Satement. Im able to convert each individual IIf statement to sql but all together I am lost. Any help will be appreciated. Thank you.

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))

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-12-15 : 17:29:13
Something like:
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]/case when 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>.55
then 55400
else 33600
end
Go to Top of Page
   

- Advertisement -