make_null and substring in modify stage.

[iwin]

Hi,
Can anyone give example of how to use make_null() (replacing @null in server)and substring(replacing trim in server) in modify stage. I tried using them with following examples but its not working:

specification= new column=make_null()
specification= new column=substring[0,7](old column)

thanks in advance.

[us1aslam1us]

Code: Select all

IN_COL = make_null(OUT_COL)

This will replace the @null value in your source column. But i prefer using handle_null in conjuction with make_null to perform this, so as to convert it to '0' or any specific value.

IN_COL=handle_null(make_null(OUT_COL),0)

relatively your second function has to be something like this..

Code: Select all

IN_COL = substring[1,7](OUT_COL)

which extracts the first seven characters of the field OUT_COL

[us1aslam1us]

Code: Select all

IN_COL = make_null(OUT_COL)

This will replace the @null value in your source column. But i prefer using handle_null in conjuction with make_null to perform this, so as to convert it to '0' or any specific value.

IN_COL=handle_null(make_null(OUT_COL),0)

relatively your second function has to be something like this..

Code: Select all

IN_COL = substring[1,7](OUT_COL)

which extracts the first seven characters of the field OUT_COL

[iwin]

Hey,
Thanks for reply. But the first one ( make_null) doesnt have an input column. @null in server is assigned in transformer when there is no input column present. Which contradictions what u r just said in modify stage make_null(input colum_name). Handle null is used to null the traget value if we have a null source value coming.
And coming to the 2nd question abt substring, the actual value pointer for substring starts with '0' not from '1'.

let me know if i missed something.

Thanks