'NULL' string not replacing in Transformer.

[snt_ds]

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

[chulett]

So... just to be clear... when you say a null string here, you actually mean a string with the value of "NULL", yes? What is the source data type?

[snt_ds]

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

[sureshabbisetti]

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

[ray.wurlod]

Please be corrected. You must handle the "genuine null" first because functions such as StringToDecimal() are intolerant of them.