I have a user-defined sql in Oracle Stage, which works fine in parallel job but failed in server job.
UPDATE
test_table
SET
IS_EXPIRED = 'Y'
WHERE
(COL_ID in (666, 667) )
error message in server job is:
ORA-01722: invalid number
Does anyone know why it won't work in server job?
Thanks in advance!
user defined sql
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Re: user defined sql
HI,
1) There is a space between "666," and "667" in your query.
2) Are your sure you are not supposed to use "." as decimal separator.
Good luck
Matthieu
1) There is a space between "666," and "667" in your query.
2) Are your sure you are not supposed to use "." as decimal separator.
Good luck
Matthieu
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
And the space isn't an issue either. It can be there or not. I'm sure it will come down to column types as mentioned earlier.
Not sure why you'd want to do a query like this, one that's not tied to the current row using bind variables / parameter markers. It will be executed once for every row sent to the stage, regardless of the contents of that row. Is that really what you had in mind?
Not sure why you'd want to do a query like this, one that's not tied to the current row using bind variables / parameter markers. It will be executed once for every row sent to the stage, regardless of the contents of that row. Is that really what you had in mind?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Basically, you have to track down why Oracle believes you've used an invalid number. The only possibility in your query is 'Y'. Check the data type of IS_EXPIRED (use DESCRIBE).
Last edited by ray.wurlod on Thu May 26, 2005 7:14 am, edited 2 times in total.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom