Hello Community,
I have a requirement at a client, where I need to substring Textual data coming from the source File and load that substring data to the target. Kindly note that its data column, not metadata and also this has to be done for some specific Accounts. So to achieve that I have used #SQL Mapping in Account.
Import Format Type = "Delimited-All Datatype"
To understand it, Following is the example of the incoming file:
Custom2,Account,Scenario,Period,Week,Entity,Business Line,Property,Year,Line Item,Event,Data
J 300406,CO_NAME,Actual,BegBalance,Default Week,McJ ACT,Default Contracts,Default Property,2018,No Line Item,No Event,ABCDEFGHIJKLMNOPQRSTUVWXYZ
J 300406,CO_DISTRICT,Actual,BegBalance,Default Week,McJ ACT,Default Contracts,Default Property,2018,No Line Item,No Event,A01XYZABCAAAAA
Now, following is the #SQL Mapping which I have written:
ACCOUNT,
AMOUNTX=
(CASE WHEN
UPPER(TO_CHAR(ACCOUNT))='CO_NAME' THEN SUBSTR(TO_CHAR(AMOUNT),3,3)
ELSE TO_CHAR(AMOUNT)
END)
Ideally, it should have worked as its very straight forward, but I am getting following SQL exception in the logs:
2019-02-08 08:02:53,314 FATAL [AIF]: Error in CommMap.updateTDATASEG_T_TDATASEGW
Traceback (most recent call last):
File "<string>", line 1611, in updateTDATASEG_T_TDATASEGW
SQLException: java.sql.SQLException: ORA-01407: cannot update ("NCDGNDKVAXG"."TDATASEG_T"."AMOUNTX") to NULL
Not sure what's wrong with this. Appreciate if anyone can help out with this.
Thanks & Regards,
Hasit Parekh.