Using Modify Stage - Substring function
Moderators: chulett, rschirm, roy
Using Modify Stage - Substring function
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?
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?
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.
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.
-
- Charter Member
- Posts: 299
- Joined: Wed Nov 13, 2002 5:38 pm
- Location: USA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
Code: Select all
NAME2:string[max=100] = substring[0,100] (NAME2)
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
Code: Select all
NAME2:string[max=100] = substring[0,99] (NAME2
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
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.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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.