Using Modify Stage - Substring function

[Lawrence]

I am trying to join Data stream 1(DS1) and Data Stream 2(DS2) on the columns DS1.NAME1(Varchar 100) = DS2.NAME2(Varchar 200). To use the Join stage the key columns should have the same name and datatype so I used a modify stage on DS2 to convert DS2.NAME2 to the proper name and Datalength using the following expression

NAME2 = substring[1,100] (NAME1)

The problem is I still get the warnings

Modify_199: When checking operator: When binding output schema variable "outRec": When binding output interface field "NAME1" to field "NAME2": Conversion from source type "string[max=200]" to result type "string": Taking substring of variable-length source string

Join_189: When checking operator: On input data set 1: When binding input interface field "NAME2" to field "NAME2": Implicit conversion from source type "string" to result type "string[max=100]": Possible truncation of variable length string.

I can get rid of those warnings by using a transform stage and mapping NAME1 to NAME2[1,100] but would prefer to use the modify stage since it is a less 'expensive' stage. Does anybody know what expression I should be using in the modify stage to get rid of the warnings?

[ArndW]

What happens if you try

NAME2:String[100] = substring[1,100] (NAME1)

[Lawrence]

I get the similiar error message

Modify_199: When checking operator: When binding output schema variable "outRec": When binding output interface field "LOGIN" to field "INVLOCATION_ID": Conversion from source type "string[max=200]" to result type "string[100]": Taking substring of variable-length source string.

[throbinson]

NAME2:String[100] = substring[1,100] (NAME1)

Make them VARCHARs (string[max=]) not CHARs (string[])
therefore

NAME2:String[max=100] = substring[1,100] (NAME1)

[ArndW]

throbinson - thanks, I felt like I was missing something when I posted that but couldn't check it with a job and just "let it slide". Good catch.

[ray.wurlod]

Beware that Modify stage uses zero-based counting. So, if you want the leftmost 100 characters, the Specification should be of the form

Code: Select all

NAME2:String[100] = substring[0,100] (NAME1) 

[Lawrence]

Unfortunately Warning message still appears. Modify expression is now

Code: Select all

NAME2:string[max=100] = substring[0,100] (NAME2)

and the warning message is

Modify_199: When checking operator: When binding output schema variable "outRec": When binding output interface field "NAME1" to field "NAME2": Conversion from source type "string[max=200]" to result type "string[max=100]": Taking substring of variable-length source string

[ArndW]

Try

Code: Select all

NAME2:string[max=100] = substring[0,99] (NAME2

[Lawrence]

No change. Same error message

[Lawrence]

No change. Same error message

[ArndW]

I just created a test job. Row Generator generates a VarChar(200) column called "InCol", the modify stage creates a column defined as VarChar(100) with a derivation of "OutCol:string[max=100]=substring[0,99 (InCol)" and get a warning of

Row_Generator_0: When checking operator: When binding output interface field "InCol" to field "OutCol": Conversion from source type "string[max=200]" to result type "string[max=100]": Taking substring of variable-length source string.

I would deprecate this warning to be either an informational message or drop it from the log completely, or do this string function in a transform stage.

[Lawrence]

Thanks, at least it's good to know I'm not missing anything

[ray.wurlod]

If you add an additional Specification to the Modify stage, containing the single word NOWARN, does the warning go away?

[Lawrence]

Yep, 'NOWARN' works. I'm assuming that this is just a generic override for any warning message in the modify stage?

[ray.wurlod]

Don't know about "any". Certainly "some". Read about it in the Orchestrate Operators manual - for some reason the NOWARN Specification is not mentioned in the Parallel Job Developer's Guide.