Transformation to make 0's appear as NULL in target

[MoeyE]

Hi,

I was wondering how to make the target columns appear as NULL if the source value is a zero. I don't want them to remain as zero in the target.

Any guidance with this is appreciated.

Kind regards,

Mo

[SachinB]

Hello @MoeyE ,

Please try the below in expression builder in order to replace 0 with Null

replace($EMPLOYEE_ID,"0","Null");

Regards,

Sachin B

[john_wang]

Hello Mo, @MoeyE 

I'd like to confirm the behavior for you. Do you mind to share what's the source and target databases type?

Regards,

John.

[Heinvandenheuvel]

@MoeyE  this works for me Oracle to Oracle . replace with your column name and test value:

case when $TXT = 'beans' then null else $TXT end

@SachinB  replace($EMPLOYEE_ID,"0","Null");

Did you try? That would put the STRING 'NULL' out there, Surely @MoeyE wants it to become a null value - 'nothing'

If you want to be cute, then for certain targets you could replace with '<att_null>' or 'attNULL' and the CSV intermediate file handling for CDC might just replace those with NULL values on target. 🙂

Hein

[MoeyE · Accepted solution]

Hi,

Thx everyone for the helpful responses. I didn't word the original question correctly, my original intent was to insert a NULL value if the length of the value in the column is 0. So this was my final expression:

Regards,

Mo