update with non-prime key in the where statement.
Posted: Mon May 24, 2004 3:37 pm
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
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