Null Handling

abhilashnair · Post by **abhilashnair** » Tue Jul 27, 2010 5:37 am

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.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 27, 2010 6:34 am

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.

abhilashnair · Post by **abhilashnair** » Tue Jul 27, 2010 6:53 am

$APT_DISABLE_COMBINATION is set to True in job parameters. The transformer is throwing the error.

ArndW · Post by **ArndW** » Tue Jul 27, 2010 7:28 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.

abhilashnair · Post by **abhilashnair** » Tue Jul 27, 2010 7:46 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

ArndW · Post by **ArndW** » Tue Jul 27, 2010 7:49 am

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".

abhilashnair · Post by **abhilashnair** » Tue Jul 27, 2010 7:54 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 ?

ArndW · Post by **ArndW** » Tue Jul 27, 2010 7:57 am

Most likely the DDL are not the same with respect to nullability between the two environments.

abhilashnair · Post by **abhilashnair** » Tue Jul 27, 2010 8:07 am

The Oracle tables are identical in both environments. If that what you mean by DDL ? It has to be the data. Obviously UAT guys pass all types of data which may not be in Dev..What say ?

ArndW · Post by **ArndW** » Tue Jul 27, 2010 8:29 am

"DDL" Data Definition Language. Do a "DESCRIBE <table>" in both environments and check the nullability in both environments for the column in question.

creatingfusion · Post by **creatingfusion** » Tue Jul 27, 2010 10:10 am

ues the function NullToEmpty in yours transformer dataset will treat blanks as null

abhilashnair · Post by **abhilashnair** » Wed Jul 28, 2010 4:03 am

ArndW wrote:"DDL" Data Definition Language. Do a "DESCRIBE <table>" in both environments and check the nullability in both environments for the column in question.

I did exactly the same thing desribed by you. The tables are identical in all respects.

abhilashnair · Post by **abhilashnair** » Wed Jul 28, 2010 4:05 am

creatingfusion wrote:ues the function NullToEmpty in yours transformer dataset will treat blanks as null

Do you mean to say IsNull function does not work in all scenarios ? Why do u suggest NullToEmpty ?

ArndW · Post by **ArndW** » Wed Jul 28, 2010 4:19 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.

abhilashnair · Post by **abhilashnair** » Wed Jul 28, 2010 4:42 am

ArndW wrote: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 messa ...

Yes it is a nullable field in both environments. I checked using DESCRIBE table.