Nested Access IIF statement to SQL Case

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources