DSXchange

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 ???

Hi

If you are updating a record or a set of record then you need to make sure that you are sending enough information to find a record or a set of records.

You can create User-Defined-SQL to do in whatever way you wanted to.



Thanks
Siva

The key data is very clearly defined, is there anyone else who can help with this problem

Thanks

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.

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

Julie, try this user-defined SQL:
If your NULLABLEKEY is made up of CHARs, use a specific string instead of -99999.

Another option would be to stop using the 'Oracle ODBC' stage and try using the native OCI stage instead. It shouldn't get... confused.

The key is NULL did not work , and we had trouble with the OCI stage, so we went with the User Defined SQL using the NVL option. Works beautifully

Thank-you