DSXchange

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?

What happens if you try

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

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.

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

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

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

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.

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

Unfortunately Warning message still appears. Modify expression is now
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

Try

No change. Same error message

No change. Same error message

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.

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

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

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

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.