Hi all,
I'm having some trouble writing a nested case statement (I think that's what I need to do).
Could someone please help me?
I have the following case statement in my query:
CASE
WHEN ALLOC_ADJUST_QTY IS NULL THEN QTY
ELSE QTY+ALLOC_ADJUST_QTY
END AS ADJUSTED_QTY,
and another column called Allocation_Sum.
What I need to do is create another case statement that says when Allocation_Sum IS NULL THEN ADJUSTED_QTY ELSE ADJUSTED_QTY - Allocation_Sum.
CASE
WHEN Allocation_Sum IS NULL THEN ADJUSTED_QTY
ELSE ADJUSTED_QTY-Allocation_Sum
END AS UNALLOCATED,
Below is my entire query:
SELECT ATLASLIVE.CO_PORDER.PO_PREFIX || '-' || ATLASLIVE.CO_PORDER.PO_NUMBER || '-' || ATLASLIVE.CO_PORDER.PO_SUFFIX AS PO, ATLASLIVE.CO_ITEM.SHORT_NAME AS ITEM_SHORT, ATLASLIVE.CO_PORDER.DELVPERIOD_FROM AS DELIVERY_DATE, ATLASLIVE.CO_INCOMING_SAMPLE.RESULT_DATE, ATLASLIVE.CO_ITEM.ITEM_NAME, ATLASLIVE.GN_SAMPLE_TYPE.TYPE_NAME, ATLASLIVE.CO_INCOMING_SAMPLE.STATUS, ATLASLIVE.CO_PORDER.NET_WEIGHT,
CASE
WHEN ALLOC_ADJUST_QTY IS NULL THEN QTY
ELSE QTY+ALLOC_ADJUST_QTY
END AS ADJUSTED_QTY,
AllocationSum.Allocation_Sum AS SumAlloc
FROM ((((ATLASLIVE.CO_INCOMING_SAMPLE INNER JOIN ATLASLIVE.CO_PORDER ON ATLASLIVE.CO_INCOMING_SAMPLE.PO_KEY = ATLASLIVE.CO_PORDER.PO_KEY) INNER JOIN ATLASLIVE.CO_ITEM ON (ATLASLIVE.CO_PORDER.ITEM_CODE = ATLASLIVE.CO_ITEM.ITEM_CODE) AND (ATLASLIVE.CO_PORDER.ITEM_CODE = ATLASLIVE.CO_ITEM.ITEM_CODE)) INNER JOIN ATLASLIVE.GN_SAMPLE_TYPE ON ATLASLIVE.CO_INCOMING_SAMPLE.TYPE_CODE = ATLASLIVE.GN_SAMPLE_TYPE.TYPE_CODE) INNER JOIN ATLASLIVE.CO_VENDOR ON (ATLASLIVE.CO_INCOMING_SAMPLE.SENDER_CODE = ATLASLIVE.CO_VENDOR.VENDOR_CODE) AND (ATLASLIVE.CO_PORDER.VENDOR_CODE = ATLASLIVE.CO_VENDOR.VENDOR_CODE)) INNER JOIN
(
SELECT ATLASLIVE.CO_ALLOCATION_TAIL.PO_KEY, Sum(ATLASLIVE.CO_ALLOCATION_TAIL.PO_ALLOC_QTY) AS Allocation_Sum
FROM ATLASLIVE.CO_ALLOCATION_TAIL
GROUP BY ATLASLIVE.CO_ALLOCATION_TAIL.PO_KEY
)AllocationSum ON ATLASLIVE.CO_PORDER.PO_KEY = AllocationSum.PO_KEY
GROUP BY ATLASLIVE.CO_PORDER.PO_PREFIX || '-' || ATLASLIVE.CO_PORDER.PO_NUMBER || '-' || ATLASLIVE.CO_PORDER.PO_SUFFIX, ATLASLIVE.CO_ITEM.SHORT_NAME, ATLASLIVE.CO_PORDER.DELVPERIOD_FROM, ATLASLIVE.CO_INCOMING_SAMPLE.RESULT_DATE, ATLASLIVE.CO_ITEM.ITEM_NAME, ATLASLIVE.GN_SAMPLE_TYPE.TYPE_NAME, DECODE(ALLOC_ADJUST_QTY,Null,QTY,QTY+ALLOC_ADJUST_QTY), AllocationSum.Allocation_Sum, ATLASLIVE.CO_INCOMING_SAMPLE.STATUS, ATLASLIVE.CO_PORDER.NET_WEIGHT, ATLASLIVE.CO_PORDER.ALLOC_ADJUST_QTY, ATLASLIVE.CO_PORDER.QTY, ATLASLIVE.GN_SAMPLE_TYPE.TYPE_CODE
HAVING (((ATLASLIVE.CO_INCOMING_SAMPLE.STATUS)='A') AND ((ATLASLIVE.GN_SAMPLE_TYPE.TYPE_CODE)=3))
Thank you again for any help