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
Checking for negative number & mapping to different colu
Moderators: chulett, rschirm, roy
Re: Checking for negative number & mapping to different
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
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
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
Phyllis,
Derivation for the CREDIT_AMOUNT column could be:
if I read your requirements correctly, anyway.
Tony
Derivation for the CREDIT_AMOUNT column could be:
Code: Select all
If CREDIT_AMOUNT >= 0 Then (CREDIT_AMOUNT * 100) Else 0
Tony