update with non-prime key in the where statement.

[dsfan2004]

Hello everyone,

I got this problem when I tried to update an Oracle table. Here is the scenario:

The primary key is an automated number. ( I generated it using keymgt.getnextvalue). There is a business key ( not nullalbe, but not a primay key ) on the table. If any new item has the same business key, then I update the existing entry. otherwise, I use keymgt.getnextvalue to generate the primary key, and insert the new item.

I used datastage to generate the SQL ( the update action is "update existing rows or insert new rows", then I change the update action to "User defied SQL" and paste the SQL generated earlier. Then I changed the where statement. ( that is, I changed from primary_key=:1 to business_key=:2 )

I guess it sounds logical, but then I got this error message:
ORA-01036: illegal variable name/num

Does anyone have a similiar experience?

You answer or suggestions will be greatly appreciated!

Thanks

[ogmios]

The :1 and :2 are supposed to be columns in the input of your job, do you have corresponding columns?

Ogmios

[chulett]

And did you change which one was marked as a 'key'?

[ketfos]

Hi,
Can you send your user defined sql?

Ketfos

[dsfan2004]

This is the SQL generated by Datastage

notice that the DEALER_KEY is the primary key. It is an auto number, I generated it by using keymgt.getnextvalue. DEALER_CODE is the actual business code.


UPDATE Dealer SET DEALER_CODE=:2,DEALER_DESC=:3 WHERE DEALER_KEY=:1;

INSERT INTO Dealer (DEALER_KEY,DEALER_CODE,DEALER_DESC) VALUES (:1,:2,:3)


This is the user defined SQL

UPDATE Dealer SET DEALER_DESC=:3 WHERE DEALER_CODE=:2;

INSERT INTO Dealer (DEALER_KEY,DEALER_CODE,DEALER_DESC) VALUES (:1,:2,:3)

[chulett]

This is the user defined SQL

UPDATE Dealer SET DEALER_DESC=:3 WHERE DEALER_CODE=:2;

INSERT INTO Dealer (DEALER_KEY,DEALER_CODE,DEALER_DESC) VALUES (:1,:2,:3)

There's your problem - all variables must be bound, so you can't get away with not mentioning :1 in your first SQL statement. And just to further check, is Column 2 marked as a 'key' in your stage now?

[dsfan2004]

No, only column 1 is the key.

This is the user defined SQL

UPDATE Dealer SET DEALER_DESC=:3 WHERE DEALER_CODE=:2;

INSERT INTO Dealer (DEALER_KEY,DEALER_CODE,DEALER_DESC) VALUES (:1,:2,:3)

There's your problem - all variables must be bound, so you can't get away with not mentioning :1 in your first SQL statement. And just to further check, is Column 2 marked as a 'key' in your stage now?