Handling Null update sql

[vij_gar]

Hi
I am running a job which updates a table using the below sql. Sql is from "Generated sql" in the Oracle8i oci stage

Update table test a
set a.date1 =sysdate
where a.pk1=:1 and a.Pk2=:2

Paramters which are both composite primary key:
:1 has null value from the input stage(not always null but sometimes)
:2 has a numeric value

when i ran the job i see 1 row updates in the log but i see date is not updated in the table???.
I know why it is not updating. oracle returns 0 row when we use column=NULL

I am sure someone might have gone through this problem,Can you please tell me how update null using the datastage?
Thanks
Jay

[ray.wurlod]

It's not a DataStage problem.

No database will let you use NULL in an "=" expression.

If you need to update a column in which a search column is null, you must use IS NULL explicitly.

So you need two outputs. One which does as you've done, but which only passes rows in which both search columns are not null.

The other handles null-valued pk1 columns.

Code: Select all

UPDATE table test a SET a.date1 = SYSDAT where a.pk1 IS NULL and a.pk2 = :1;

You don't even supply a column for pk1 (hence the :1 parameter marker for pk2).

This design assumes that pk2 is never null, otherwise more complexity of design is required.

However, your names suggest that these columns are primary key columns. To be primary key columns they must be declared to be NOT NULL, so how is it you're getting NULL values? Or, if you're not, then the condition WHERE a.pk1 IS NULL can never be satisfied; you will need to transform the incoming null value to some default.

There are supplied Transforms such as NullToZero and NullToEmpty (in the Transforms branch, category Built-In\Null) that may help with this.

[GIDs]

Hi
I am running a job which updates a table using the below sql. Sql is from "Generated sql" in the Oracle8i oci stage

Update table test a
set a.date1 =sysdate
where a.pk1=:1 and a.Pk2=:2

Paramters which are both composite primary key:
:1 has null value from the input stage(not always null but sometimes)
:2 has a numeric value

when i ran the job i see 1 row updates in the log but i see date is not updated in the table???.
I know why it is not updating. oracle returns 0 row when we use column=NULL

I am sure someone might have gone through this problem,Can you please tell me how update null using the datastage?
Thanks
Jay

You can check for NULLs using the oracle NVL function within the user-defined SQL or use IsNull and setNull functions within a transformer