I am selecting fields off of a Netezza Table, of which a few have null values. I use the Neteeza Database stage to read the table. I tried to handle the nulls in a subsequent transformer using [ If isNull(column) then -- else -- ] and NullToValue but the job would abort with the following error.
Null in field "sample"; the result is non-nullable and there is no handle_null to specify a default value.
However, when I modified the select query to include nvl functions, the job ran successfully. The jobs is [Netezza Stage] --> Transformer --> Peek.
Any ideas on how can I handle the nulls without specifying a user-generated query? I would like to use the system generated query. Further, since I am not sure what columns may or may not have nulls, I have specified the nullability as unknown in column properties.
"When checking operator: When binding input interface field "sample" to field "sample": Converting a nullable source to a non-nullable result;a fatal runtime error could occur;use a modify operator to specify the value to which the null should be converted."
Its the neteeza stage that is calling you grief, I don't have neteeza but you should be able to set a null handle in the field properties of the stage. DataStage is getting the metadata from the database and according to it the data should not be null. I must admit I am curious why you would get this error at all if a null is stored in the database. I bet if you put a trim around the field in sql it would work as well, because the function does not allow datastage to understand the nullability so it trusts what you have input on the metadata.
I believe I misled you, you aren't going to be able to handle null one fo the properties. Had to get in front of a datastage client to see it.
Can you verify that the table allows nulls in the field? Datastage should be reading the table metadata so I would be curious how it is getting a null in the first place.
kwwilliams wrote:...Can you verify that the table allows nulls in the field? Datastage should be reading the table metadata so I would be curious how it is getting a null in the first place.
There do exist NULL in the fields. I can see them when I read the table in a SQL client.
kwwilliams wrote:...Can you verify that the table allows nulls in the field? Datastage should be reading the table metadata so I would be curious how it is getting a null in the first place.
There do exist NULL in the fields. I can see them when I read the table in a SQL client.
Can you look at the DDL? This is where DataStage is getting its metadata on the table.
kwwilliams wrote: Can you look at the DDL? This is where DataStage is getting its metadata on the table.
Well, I do know that the table contains NULLs since I can see them when I read the table in SQL client. And I have clearly specified null handling in the transformer. Do you mean to say that the DDL doesn't mention anything about NULLs which leads DataStage to falsely believe that there are no NULLs and so, it throws up an error when it encounters NULLs?
When DataStage makes the connection it sends the sql statement to the database and run time metadata is returned, which is why you get the warning in the Neteeza said:
"When checking operator: When validating import schema: At field "sample": Importing potential null to not nullable field"
It is validating that the run time and design time metadata match and its complaining that they don't. You said that the design metadata is set to Null or Unknown.
When you used the NVL function in sql it can't obtain information from the database catalog about this field because it is a function call that is being returned so it assumes null and didn't complain any longer. To be honest when I read this warning from the Neteeza stage I think it is saying that your design time metadata specifies not null, but your run time metadata specifies null.
If we could see the dump score for your job and the DDL for the table (at least the offending column, we should be able to quickly track down your problem.
Any field which is 'Nullable' needs to be checked for 'IsNull' then only you can do the derivations in the transformer. The logic being if the column is 'nullable' and if you try to do some derivation like 'substring' then basically you are trying do a 'substring' on 'null' values which will throw a null pointer exception.
Hope i am clear.
Sreenivasulu wrote:Any field which is 'Nullable' needs to be checked for 'IsNull' then only you can do the derivations in the transformer.....
I do have the [ If isNull(column) then -- else -- ] in my transformer as I mentioned in my first post. Yet the job fails. The job only works if I specify nvl in the select query in the Netezza stage.