IIF statement help

[craig198101]

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

 

[xenou]

Hi,
what are the primary keys in the tables? It will be hard to write any queries without that information.

 

[craig198101]

Thank you for the reply the primary keys are FIFOCMSData: ReceiptSupplement(invoice number) and QTYFIFOWH: Invoice Number

 

[xenou]

Hmm. Actually it can't be done because you want to copy a record:

1. Copy original Oracle FIFO record to INSERT brand new Oracle FIFO record with “TxnQty” = “TxnQty” - “REMAINING QTY” and “StatusCode” = ‘D’

But if you do that you will be copying the primary key as part of the new record. And you cannot have two records with the same primary key.

 

[craig198101]

Thank you Xenou, I managed to resolve the issue using a temp table.

Thanks again