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
update with non-prime key in the where statement.
Moderators: chulett, rschirm, roy
Re: update with non-prime key in the where statement.
The :1 and :2 are supposed to be columns in the input of your job, do you have corresponding columns?
Ogmios
Ogmios
here is the SQL
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)
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)
Re: here is the SQL
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 wrote: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)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: here is the SQL
No, only column 1 is the key.
chulett wrote: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 wrote: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)