Hi Dsxians,
I am experiencing a strange problem in transformer stage for some columns and for some datastage
jobs alone ,when i am trying to replace a 'NULL' string from source with setnull() function for a target
decimal column 28,12.Derivation is below.
If Trim(ACTION_SRC_3_PAMPositionAfterExchangeJoin.BOOK_VALUE)='NULL' Then setnull() Else StringToDecimal(ACTION_SRC_3_PAMPositionAfterExchangeJoin.BOOK_VALUE)
Even if i am not mapping this column with 'NULL' value to target I am getting this below warning.
TRN_POS_VALIDATIONS,0: A numeric string was expected, got "NULL"; using Decimal, the default value. [transform/tfmop_functions.C:448]
Could you please let me know any suggessions or thoughts.Thanks for your time.
Thanks
snt_ds
'NULL' string not replacing in Transformer.
Moderators: chulett, rschirm, roy
Thanks chulett for the reply.
Yes. A string with value of "NULL".If source file column is empty or NULL value then i am replacing with "NULL" string.Datatype is varchar.
IF IsNull(ACTION_SRC_1_PositionDataFromPAM.BOOK_VALUE) Then 'NULL' Else if Trim(ACTION_SRC_1_PositionDataFromPAM.BOOK_VALUE) = '' then 'NULL' else Trim(ACTION_SRC_1_PositionDataFromPAM.BOOK_VALUE)
Later replacing this string with setnull() in final dataset for target decimal column 28,12.
If Trim(ACTION_SRC_3_PAMPositionAfterExchangeJoin.BOOK_VALUE)='NULL' Then setnull() Else StringToDecimal(ACTION_SRC_3_PAMPositionAfterExchangeJoin.BOOK_VALUE)
Thanks
snt_ds
Yes. A string with value of "NULL".If source file column is empty or NULL value then i am replacing with "NULL" string.Datatype is varchar.
IF IsNull(ACTION_SRC_1_PositionDataFromPAM.BOOK_VALUE) Then 'NULL' Else if Trim(ACTION_SRC_1_PositionDataFromPAM.BOOK_VALUE) = '' then 'NULL' else Trim(ACTION_SRC_1_PositionDataFromPAM.BOOK_VALUE)
Later replacing this string with setnull() in final dataset for target decimal column 28,12.
If Trim(ACTION_SRC_3_PAMPositionAfterExchangeJoin.BOOK_VALUE)='NULL' Then setnull() Else StringToDecimal(ACTION_SRC_3_PAMPositionAfterExchangeJoin.BOOK_VALUE)
Thanks
snt_ds
-
- Participant
- Posts: 21
- Joined: Thu Oct 04, 2007 4:34 am
- Location: India
- Contact:
Hi
Try with below logic
If Trim(ACTION_SRC_3_PAMPositionAfterExchangeJoin.BOOK_VALUE)='' Then setnull() Else StringToDecimal(ACTION_SRC_3_PAMPositionAfterExchangeJoin.BOOK_VALUE)
Otherwise use stage variable to check the column value is null or not
Please correct me If I am wrong
Thanks
Suresh
Try with below logic
If Trim(ACTION_SRC_3_PAMPositionAfterExchangeJoin.BOOK_VALUE)='' Then setnull() Else StringToDecimal(ACTION_SRC_3_PAMPositionAfterExchangeJoin.BOOK_VALUE)
Otherwise use stage variable to check the column value is null or not
Please correct me If I am wrong
Thanks
Suresh
Thanks & Regards
Suresh Kumar Abbisetti
Suresh Kumar Abbisetti
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: