Nullable warning
Moderators: chulett, rschirm, roy
Nullable warning
Hi,
I have a job with XML stage as input to the job and it contains XML input stage . I have two columms which contains both null and non null values for the two columms . I gave in Output page for those two columms nullable property to Yes and I am loading these two columm data to a sequential file through transformer and I gave nullable property in sequential file for those two columms to Yes . Iam facing this warning how to over come this . I gave nullable yes for both input and output then why Iam getting this error and plz tell me how to solve this warning.
Warning
Sequential_Reject: When checking operator: When validating export schema: At field "PaymentTerms": Exporting nullable field without null handling properties
Thanks
Somaraju
I have a job with XML stage as input to the job and it contains XML input stage . I have two columms which contains both null and non null values for the two columms . I gave in Output page for those two columms nullable property to Yes and I am loading these two columm data to a sequential file through transformer and I gave nullable property in sequential file for those two columms to Yes . Iam facing this warning how to over come this . I gave nullable yes for both input and output then why Iam getting this error and plz tell me how to solve this warning.
Warning
Sequential_Reject: When checking operator: When validating export schema: At field "PaymentTerms": Exporting nullable field without null handling properties
Thanks
Somaraju
somaraju
Since the column is specified as Nullable, Datastage expectes null for the column should be handled prior to load. Either you can use transformer/modify stage to handle explicitly or as mention Nullable field value to specify implict null handling.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Hi Kumar,
The columm is decimal (7,4) . When Iam using a modify stage . Iam getting this error . I tried with string also I got the same error
PaymentTerms:Decimal = NullToValue (PaymentTerms,"Null")
ContractType:Decimal = NullToValue (ContractType,"Null")
main_program: Error parsing modify adapter: Error in binding: Unknown conversion: NullToValue
Expected destination field selector, got: ")"; input:
PaymentTerms:string = NullToValue (PaymentTerms,"Null")
;
ContractType:string= NullToValue (ContractType,"Null")
;
Error parsing modify adapter: Error in binding: Unknown conversion: NullToValue
Expected destination field selector, got: ")"; input:
PaymentTerms:string = NullToValue (PaymentTerms,"Null")
;
ContractType:string = NullToValue (ContractType,"Null")
Thanks,
Somaraju
The columm is decimal (7,4) . When Iam using a modify stage . Iam getting this error . I tried with string also I got the same error
PaymentTerms:Decimal = NullToValue (PaymentTerms,"Null")
ContractType:Decimal = NullToValue (ContractType,"Null")
main_program: Error parsing modify adapter: Error in binding: Unknown conversion: NullToValue
Expected destination field selector, got: ")"; input:
PaymentTerms:string = NullToValue (PaymentTerms,"Null")
;
ContractType:string= NullToValue (ContractType,"Null")
;
Error parsing modify adapter: Error in binding: Unknown conversion: NullToValue
Expected destination field selector, got: ")"; input:
PaymentTerms:string = NullToValue (PaymentTerms,"Null")
;
ContractType:string = NullToValue (ContractType,"Null")
Thanks,
Somaraju
somaraju
Hi,
I want to load a null value in to sequential file . Some rows contains null value and columm length for that which contains null value is Decimal(7,4). I am getting warning and fatal errors as I mentioned above . Plz help me how to load null values in to a sequential file with out warnings and fatal errors.
Thanks,
Somaraju.
I want to load a null value in to sequential file . Some rows contains null value and columm length for that which contains null value is Decimal(7,4). I am getting warning and fatal errors as I mentioned above . Plz help me how to load null values in to a sequential file with out warnings and fatal errors.
Thanks,
Somaraju.
somaraju
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is an error in the Parallel Job Developer's Guide. The NullToValue() function pertains only to the Transformer stage. What you need in a Modify stage is the handle_null() function. You can learn this from the Orchestrate Operators manual - the chapter on the Modify operator.
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.
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
I assume you do not need a fixed length file.
I thought the original requirement was to load a null value into the file? If so, changing the value to something which is not null is not loading a null value into the file. A null is the absence of a value. Thus, for a sequential file it would be the field delimeter followed by another field delimeter.
I was able to load nulls into sequential files by setting the sequential file -> format -> Field Defaults -> Null Field Value=""
"" (Empty string).
This gave me the following two errors:
1. Sequential_File_3: When checking operator: When validating export schema: At field "b": "null_field" length (0) must match field's fixed width (9)
2. Sequential_File_3: When checking operator: When validating export schema: At field "b": Exporting a fixed length field with a "null_field" of length 0 will prohibit a re-import of this field
However, the data loaded into the file correctly and the file could be read correctly without any problems.
I thought the original requirement was to load a null value into the file? If so, changing the value to something which is not null is not loading a null value into the file. A null is the absence of a value. Thus, for a sequential file it would be the field delimeter followed by another field delimeter.
I was able to load nulls into sequential files by setting the sequential file -> format -> Field Defaults -> Null Field Value=""
"" (Empty string).
This gave me the following two errors:
1. Sequential_File_3: When checking operator: When validating export schema: At field "b": "null_field" length (0) must match field's fixed width (9)
2. Sequential_File_3: When checking operator: When validating export schema: At field "b": Exporting a fixed length field with a "null_field" of length 0 will prohibit a re-import of this field
However, the data loaded into the file correctly and the file could be read correctly without any problems.
Hi Ultramundane,
If I do it like this -> format -> Field Defaults -> Null Field Value="". When I load from Sequential file to a DB2 table, will this load a null value in the table . I think no because it is not a null value since '' '' is not a null value . Can I use NULLTOEmpty null handling function to load a null value .
Thanks,
Somaraju.
If I do it like this -> format -> Field Defaults -> Null Field Value="". When I load from Sequential file to a DB2 table, will this load a null value in the table . I think no because it is not a null value since '' '' is not a null value . Can I use NULLTOEmpty null handling function to load a null value .
Thanks,
Somaraju.
somaraju
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
To get to that, right-click on a row in the Columns grid and choose Edit Row from the menu. Note that you can move to the next or previous column without leaving the Edit Metadata dialog. Note also that you must Apply or abandon any change before moving on: the dialog reminds you to do so.
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.
Hi ,Ultramundane,
I was able to load nulls into sequential files by setting the sequential file -> format -> Field Defaults -> Null Field Value=""
"" (Empty string).
This gave me the following two errors:
1. Sequential_File_3: When checking operator: When validating export schema: At field "b": "null_field" length (0) must match field's fixed width (9)
2. Sequential_File_3: When checking operator: When validating export schema: At field "b": Exporting a fixed length field with a "null_field" of length 0 will prohibit a re-import of this field
I tried by giving padchar = null and space in general in Format tab of sequential file but Iam getting the same warning please can any one tell me how to get rid of this warning .
Thanks,
Somaraju
I was able to load nulls into sequential files by setting the sequential file -> format -> Field Defaults -> Null Field Value=""
"" (Empty string).
This gave me the following two errors:
1. Sequential_File_3: When checking operator: When validating export schema: At field "b": "null_field" length (0) must match field's fixed width (9)
2. Sequential_File_3: When checking operator: When validating export schema: At field "b": Exporting a fixed length field with a "null_field" of length 0 will prohibit a re-import of this field
I tried by giving padchar = null and space in general in Format tab of sequential file but Iam getting the same warning please can any one tell me how to get rid of this warning .
Thanks,
Somaraju
somaraju
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
I am making an assumption that the datatype you are using is a char?
Most DBMS's don't care if the character datatype is varchar or char. In fact, for many, a char null is actually defined internally as a varchar null. Which I think would be technically correct. A char null is not fixed width. It either contains no characters or some characeters equal to its length and padded if not that long.
Anyways, I have to use varchar null instead of a char null when using this method. It seems that datastage cannot work with char null when using sequential files. Using a varchar has not caused me any issues to date because of the underlying DBMS's ability to work with chars and varchars interchangeably. It would be nice if datastage could function as elegantly.
Most DBMS's don't care if the character datatype is varchar or char. In fact, for many, a char null is actually defined internally as a varchar null. Which I think would be technically correct. A char null is not fixed width. It either contains no characters or some characeters equal to its length and padded if not that long.
Anyways, I have to use varchar null instead of a char null when using this method. It seems that datastage cannot work with char null when using sequential files. Using a varchar has not caused me any issues to date because of the underlying DBMS's ability to work with chars and varchars interchangeably. It would be nice if datastage could function as elegantly.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
DataStage can handle a NULL in a Char(N) column, but the value that is used to represent NULL must have exactly N characters. Such is the definition of Char(N), surely?
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.
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact: