Oracle Update Query problem
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 8
- Joined: Mon Jan 24, 2005 6:28 am
- Location: Lisbon, Portugal
- Contact:
Oracle Update Query problem
Hi all,
I have a job that is supposed to do something like this:
File -> Transform -> Update Oracle Table
The problem resides on the "where clause" of my Custom Update Query:
update xpto set field1=:1 where field2 in (:2)
It seems that the "in" part of the query is completely ignored and no update is done.
Help! Anyone?
Thanks
I have a job that is supposed to do something like this:
File -> Transform -> Update Oracle Table
The problem resides on the "where clause" of my Custom Update Query:
update xpto set field1=:1 where field2 in (:2)
It seems that the "in" part of the query is completely ignored and no update is done.
Help! Anyone?
Thanks
Hi Girrobalopes,
I was facing a similiar problem.
Where do you define your Custom Update Query.
Is it defined in the target Oracle table stage.
I was getting the error message
"ORA-01036: illegal variable name/number" when I defined the user defined sql in target Oracle table stage and input is flat file stage.
Ketfos
I was facing a similiar problem.
Where do you define your Custom Update Query.
Is it defined in the target Oracle table stage.
I was getting the error message
"ORA-01036: illegal variable name/number" when I defined the user defined sql in target Oracle table stage and input is flat file stage.
Ketfos
-
- Participant
- Posts: 8
- Joined: Mon Jan 24, 2005 6:28 am
- Location: Lisbon, Portugal
- Contact:
Hi,
I had that same problem too, but it had to do with the column names in the transoformer that didn't match the table layout. After correcting these, I was able to run successfully the job, however th update was not done. My guess is that the field with the multiple vaues for the "in" restriction is being messed up by DS...
I had that same problem too, but it had to do with the column names in the transoformer that didn't match the table layout. After correcting these, I was able to run successfully the job, however th update was not done. My guess is that the field with the multiple vaues for the "in" restriction is being messed up by DS...
It's not DataStage, you can't bind a list of values in a query without some special Oracle tricks. It's an actual bind, it's not like the :2 will be replaced by the string in your second columngilrobalopes wrote:Hi,
I had that same problem too, but it had to do with the column names in the transoformer that didn't match the table layout. After correcting these, I was able to run successfully the job, however th update was not done. My guess is that the field with the multiple vaues for the "in" restriction is being messed up by DS...
Ogmios
In theory there's no difference between theory and practice. In practice there is.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If a parameter marker is to be used in the WHERE clause, it must be marked as a Key column in the DataStage column definitions. It is irrelevant whether it is actually a key in the database; the concept here (on a reference input link) is that of a "search key".
You are permitted to lie about "keys" in this case.
You are permitted to lie about "keys" in this case.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
Hi ,
I had a Oracle stage in server job which does update else insert, now if it was updating I did not want to update one of the columns, and it gives the same error
ORA-01036: illegal variable name/number
Can we not have different number of columns for update and insert statements in the same stage?
I did try with array size 1.
Thanks,
I had a Oracle stage in server job which does update else insert, now if it was updating I did not want to update one of the columns, and it gives the same error
ORA-01036: illegal variable name/number
Can we not have different number of columns for update and insert statements in the same stage?
I did try with array size 1.
Thanks,
-
- Participant
- Posts: 49
- Joined: Fri Nov 11, 2005 10:19 am
Hi Craig,
I can use a lookup to bifurcate into 2 links , one for update and one for insert but the target has close to 10 million rows and I am sure its not a easy lookup.
Basically we have record_dreate_date and record_update_date, I do not want th estage to update record_create_date when it is really updating an existing row!
Thanks,
I can use a lookup to bifurcate into 2 links , one for update and one for insert but the target has close to 10 million rows and I am sure its not a easy lookup.
Basically we have record_dreate_date and record_update_date, I do not want th estage to update record_create_date when it is really updating an existing row!
Thanks,
ALISO
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can have different no. of columns in the Update and Insert Clause in a job. i tried and the job ran successfully. In my job i didnt want to update some of the columns But while inserting i wanted it to hardcoded to some value. so i never brought these columns in the Output stage and in the insert clause i added extra columns along with the hardcoded value. And it worked!!chulett wrote:No, you cannot. If you need different column sets for insert versus update, you'll need two links. ...
Avinash
Yes, you can force it to work in a single link by using custom / user-defined sql and hard-coding any 'missing' values as you've found. I, for one, would never take that approach but if others are comfortable with it...
You are much better off using two links and specifically handling inserts versus update rather than the 'crutch' of the dual-purpose update actions of insert-then-update or update-then-insert. IMHO, of course. At least then you'll know how many of each type were performed and not worry that, for the second action to happen the first action must be attempted and fail. Yerk.
You are much better off using two links and specifically handling inserts versus update rather than the 'crutch' of the dual-purpose update actions of insert-then-update or update-then-insert. IMHO, of course. At least then you'll know how many of each type were performed and not worry that, for the second action to happen the first action must be attempted and fail. Yerk.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers