September 8, 2003 at 10:31 am
I have the MS access query below which uses an IIF statement to put a value in either the Owned Pallets or Rented Pallets field, how do I do this in SQL ?? ... The rest of the code is easy to change...
SELECT KC_COPA1.[Posting date], KC_COPA1.SKUCode, KC_COPA1.HierLevel3Code, KC_COPA1.HierLevel5Code, KC_COPA1.LineNo, KC_COPA1.EndMarket, KC_COPA1.Dispenser, format(Sum(KC_COPA1.HA),"0.0000000000") AS Handling, format(Sum(KC_COPA1.ST),"0.0000000000") AS Storage, format(Sum(KC_COPA1.PF),"0.0000000000") AS [Primary Freight], format(Sum(KC_COPA1.IR),"0.0000000000") AS [InterRegion Freight], Format(Sum((IIf(KC_COPA1!MILLPAL="O",[MillPA],0)+IIf(KC_COPA1!RDC1PAL="O",[RDC1PA],0)+IIf(KC_COPA1!RDC2PAL="O",[RDC2PA],0))),"0.0000000000") AS [Pallets Owned], Format(Sum(KC_COPA1.SACost),"0.0000000000") AS SA, Format(Sum((IIf(KC_COPA1!MILLPAL="R",[MillPA],0)+IIf(KC_COPA1!RDC1PAL="R",[RDC1PA],0)+IIf(KC_COPA1!RDC2PAL="R",[RDC2PA],0))),"0.0000000000") AS [Pallets Rented], Format(Sum(KC_COPA1.ODCost),"0.0000000000") AS Other, Format(Sum(KC_COPA1.DA),"0.0000000000") AS Admin, Format(Sum(KC_COPA1.CDPCost),"0.0000000000") AS SumOfCDPCost INTO [R3 Results_Rev]
FROM KC_COPA1
GROUP BY KC_COPA1.[Posting date], KC_COPA1.SKUCode, KC_COPA1.HierLevel3Code, KC_COPA1.HierLevel5Code, KC_COPA1.LineNo, KC_COPA1.EndMarket, KC_COPA1.Dispenser;
Thanks, John
September 8, 2003 at 7:21 pm
Look at the CASE statement in BOL.
September 9, 2003 at 7:25 am
Thanks, sorry for the simple question... now I see the answer...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply