When inserting a record using the Oracle ODBC Stage originally, where a column of the key is NULL, the insert goes fine. But when it comes to updating this record, ODBC gets confused and can't seem to update. It is like it cannot recognise that the record exists. If the coulmn which is NULL is actually given a value everything works great.
So how can we successfully update records where part of the key is NULL ???
Null value in Key - ODBC stage Input/Update
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 12
- Joined: Tue Jul 06, 2004 11:54 pm
-
- Participant
- Posts: 12
- Joined: Tue Jul 06, 2004 11:54 pm
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
It could be that your output stage is building a SQL update statement "COL1 = NULL" and not finding a result. "COL1 is NULL" is what databases usually expect. You can test this by running some update statements directly against the database to see what works, or tracing the SQL of a DataStage job and running failed statements in a SQL session.
I can't think of a solution other then writing a complex user defined SQL or removing that field from the primary key.
I can't think of a solution other then writing a complex user defined SQL or removing that field from the primary key.
Last edited by vmcburney on Mon Nov 08, 2004 12:22 am, edited 1 time in total.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
You need to do little bit of debugging work there. Send the output to a sequential output and check manually what the values you are getting as output. And for the output value in the sequential try to make your sql statement manually in your SQL prompt and execute. See what does it says.....
Best of luck
Siva
Best of luck
Siva
Julie, try this user-defined SQL:
If your NULLABLEKEY is made up of CHARs, use a specific string instead of -99999.
Code: Select all
UPDATE YOURTABLE SET COL1=?,COL2=?,COL3=?
WHERE KEY1=? AND KEY2=? AND NVL(NULLABLEKEY,-99999) = NVL(?,-99999)
-
- Participant
- Posts: 12
- Joined: Tue Jul 06, 2004 11:54 pm