craig198101
New Member
- Joined
- Mar 16, 2016
- Messages
- 5
Hi all,
I am trying to create a query to append records into a an existing table. I need the query to include the following IIF statement:
IF “REMAINING QTY” is less than Oracle FIFO “TxnQty”
THEN
1. Copy original Oracle FIFO record to INSERT brand new Oracle FIFO record with “TxnQty” = “TxnQty” - “REMAINING QTY” and “StatusCode” = ‘D’
2. UPDATE original Oracle FIFO “TxnQty” = “REMAINING QTY”, leaving “StatusCode” = ‘N’
END IF
Tables:
FIFOCMSData holds TxnQty and StatusCode
QTYFIFOWH holds REMAINING QTY and TxnQty1
I have managed to do part one with two IIF:
1.TxnQty: IIf([SumOfREMAINING QTY]<[TxnQty1],[TxnQty1]-[SumOfREMAINING QTY],"0")
2.StatusCode: IIf([SumOfREMAINING QTY]<[TxnQty1],"D","N")
Any ideas how to either do one nested iif(with example) or best way to create the above would be a massive help.
Big thank you in advance
I am trying to create a query to append records into a an existing table. I need the query to include the following IIF statement:
IF “REMAINING QTY” is less than Oracle FIFO “TxnQty”
THEN
1. Copy original Oracle FIFO record to INSERT brand new Oracle FIFO record with “TxnQty” = “TxnQty” - “REMAINING QTY” and “StatusCode” = ‘D’
2. UPDATE original Oracle FIFO “TxnQty” = “REMAINING QTY”, leaving “StatusCode” = ‘N’
END IF
Tables:
FIFOCMSData holds TxnQty and StatusCode
QTYFIFOWH holds REMAINING QTY and TxnQty1
I have managed to do part one with two IIF:
1.TxnQty: IIf([SumOfREMAINING QTY]<[TxnQty1],[TxnQty1]-[SumOfREMAINING QTY],"0")
2.StatusCode: IIf([SumOfREMAINING QTY]<[TxnQty1],"D","N")
Any ideas how to either do one nested iif(with example) or best way to create the above would be a massive help.
Big thank you in advance