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
Handling Null update sql
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.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.
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;
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.
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.
Re: Handling Null update sql
You can check for NULLs using the oracle NVL function within the user-defined SQL or use IsNull and setNull functions within a transformervij_gar wrote: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