Null value in Key - ODBC stage Input/Update

juliejarrett · Post by **juliejarrett** » Sun Nov 07, 2004 8:20 pm

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

rasi · Post by **rasi** » Sun Nov 07, 2004 9:48 pm

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

juliejarrett · Post by **juliejarrett** » Sun Nov 07, 2004 11:08 pm

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

Thanks

vmcburney · Post by **vmcburney** » Sun Nov 07, 2004 11:36 pm

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.

rasi · Post by **rasi** » Mon Nov 08, 2004 12:14 am

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

ailuro · Post by **ailuro** » Mon Nov 08, 2004 1:02 am

Julie, try this user-defined SQL:

Code: Select all

UPDATE YOURTABLE SET COL1=?,COL2=?,COL3=? 
WHERE KEY1=? AND KEY2=? AND NVL(NULLABLEKEY,-99999) = NVL(?,-99999)

If your NULLABLEKEY is made up of CHARs, use a specific string instead of -99999.

chulett · Post by **chulett** » Mon Nov 08, 2004 8:25 am

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

juliejarrett · Post by **juliejarrett** » Mon Nov 08, 2004 9:44 pm

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