Null Handling
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
Null Handling
Is there any scenario where IsNull function fails to catch a null value ? My source is Oracle table. Version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production. DataStage Version is 8.1
The field in question is of datatype NVARCHAR2(75) in the database. It is a Nullable field in DB.
In the DS job I am using an Oracle Enterprise Stage and have defined metadata for this field as Varchar.
In a transformer I am using If IsNull(colname) Then " " Else Colname. The output link of transformer has Nullability set to 'No' for this field
When the column has a null I want space in output else i want straight move. The output target is dataset. The issue I am facing is that the job aborts with the foll, error
APT_CombinedOperatorController(2),0: Null in field "colname"; the result is non-nullable
and there is no handle_null to specify a default value
When I queried the Database there are no nulls in the above field.
The field in question is of datatype NVARCHAR2(75) in the database. It is a Nullable field in DB.
In the DS job I am using an Oracle Enterprise Stage and have defined metadata for this field as Varchar.
In a transformer I am using If IsNull(colname) Then " " Else Colname. The output link of transformer has Nullability set to 'No' for this field
When the column has a null I want space in output else i want straight move. The output target is dataset. The issue I am facing is that the job aborts with the foll, error
APT_CombinedOperatorController(2),0: Null in field "colname"; the result is non-nullable
and there is no handle_null to specify a default value
When I queried the Database there are no nulls in the above field.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Disable operator combination, that you may learn which operator (stage) is actually throwing the error. It might be easier to diagnose what is happening once you've done that.
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: 284
- Joined: Fri Oct 13, 2006 4:31 am
If $APT_DISABLE_COMBINATION is set to "true" then your run will not contain any references to combined operators. Check the first entry in the director log for the run and you will see the runtime value for this environment variable. Correct that error and re-run to localize the stage.
Remember that the actual column nullability comes from the database, not from what is marked in the database stage metadata. This can be a factor if your SELECT contains a join or other operation on columns.
Remember that the actual column nullability comes from the database, not from what is marked in the database stage metadata. This can be a factor if your SELECT contains a join or other operation on columns.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
At project level $APT_DISABLE_COMBINATION = False..It is something I dont have a control and cannot change since it is in UAT environment...In the Dev environment, I am not able to reproduce the above error...So not sure what I can do.But i remeber a long time back of some post somewhere which said some bug with Isnull..i wonder whether thats the case
You may not be able to change the default value, but you can change the runtime value. Declare the parameter in your job and at runtime specify "true" rather than the default "false".
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
The job is in read only mode and I am wont be able to run it..I tried to replicate in Dev env but it ran fine...So I reckon it has something to do with the incoming data from Oracle. Something which is a null but is not being caught by IsNull and it is letting it pass through..and then it is failing? What do you think ?
Most likely the DDL are not the same with respect to nullability between the two environments.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
"DDL" Data Definition Language. Do a "DESCRIBE <table>" in both environments and check the nullability in both environments for the column in question.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 46
- Joined: Tue Jul 20, 2010 1:26 pm
- Location: USA
- Contact:
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am
The IsNull() function works in all scenarios. Did you check the nullability in the DESCRIBE of "colname" in both environments? It would seem to be a nullable field and this generating your error message.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 284
- Joined: Fri Oct 13, 2006 4:31 am