Checking for negative number & mapping to different colu

[phyllis]

I'm processing a .txt file containing a debit amount and a credit amount (both numeric (15,2) and writing those two amounts to an Oracle table. The .txt file contains a sign on negative amounts, so when writing to the Oracle table I'm removing the sign with this derivation Abs(Data_In.DEBIT_AMOUNT) * 100 (decimals do no appear in the Oracle table, e.g. 10.00 becomes 1000 in Oracle.

The problem I'm having is that the .txt may contain a 'negative' debit. debit_amount may be -100.00. In that case I need to move the amount to the credit_amount column instead, along with the above derivation to remove the sign, and make debit_amount zero.

How can move the value to CREDIT_AMOUNT while deriving DEBIT_AMOUNT. For DEBIT_AMOUNT (in words) I would want to say If Data_In.DEBIT_AMOUNT < 0 then move to Data_In.CREDIT_AMOUNT ELSE Abs(IBS_Data_In.DEBIT_AMOUNT) * 100

[phyllis]

Okay, now I have half the derivation accomplished with this derivation for writing to the DEBIT_AMOUNT column:

If Data_In.CREDIT_AMOUNT < 0 then Abs(Data_In.CREDIT_AMOUNT * 100) ELSE Abs(Data_In.DEBIT_AMOUNT * 100) column

but now I need to move zero to the CREDIT_AMOUNT column If Data_In.CREDIT_AMOUNT < 0

[tonystark622]

Phyllis,

Derivation for the CREDIT_AMOUNT column could be:

Code: Select all

If CREDIT_AMOUNT >= 0 Then (CREDIT_AMOUNT * 100) Else 0

if I read your requirements correctly, anyway.

Tony