In DS job we have DRS Lookup ( Database is Oracle ) stage. Where we have query
Select curr1 , curr2 , factor
from table
where curr2 = :1 or curr2 = :2
Then in transformer we are adding expression from input link which is mapped to curr2 field.
link1.curr or link1.quote_curr.
Then I am getting this error ORA 1008 Not all variables bound.
Can you tell me the solution ? Am i writing query in DRS stage in wrong way ?
Thanks
Look up Query with OR condition getting error ORA 1008
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need two key columns in your metadata. One is mapped to each parameter marker in the WHERE clause. They must (naturally) have different names, but each can have its Derivation as tablename.curr2.
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.
Re: Look up Query with OR condition getting error ORA 1008
While not the cause of your error, but an error none-the-less: do not put expressions in your Key unless all you truly want to populate it with is TRUE or FALSE, i.e. 1 or 0.ds_my_job wrote:Then in transformer we are adding expression from input link which is mapped to curr2 field.
link1.curr or link1.quote_curr
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
so
Thanks for update...So i can't use OR condition in look up expressionn ? It's giving me ora 1008 error Also on db stage if create different alias for curr2 curr2_dummy it does not allow me to use it in where clause ?
Thanks
Amit
Thanks
Amit
so
Mark fields as Key fields to allow their use in the where clause. And while you can certainly use an 'OR' in the Key Expression, it needs to be an assignment statement not just an expression. So...
Not good: "A or B"
Good: If X = "A" or X = "B" Then "Y" else "Z"
Not good: "A or B"
Good: If X = "A" or X = "B" Then "Y" else "Z"
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I remember this being a bug with Datastage 7.5.1A
Your look up using Hashed files would work correctly, but the look up against the OCI stage / DRS stage would result in the error "All Variables not bound".
I would suggest that you get in touch with IBM for the patch.
Your look up using Hashed files would work correctly, but the look up against the OCI stage / DRS stage would result in the error "All Variables not bound".
I would suggest that you get in touch with IBM for the patch.
Simplicity is the ultimate sophistication
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
Have you checked the order of these columns?
i.e. are these really your first and second columns?
Is is a user-defined query or an auto generated query?
Try to use an auto generated query and see how it binds the data.
i.e. are these really your first and second columns?
Is is a user-defined query or an auto generated query?
Try to use an auto generated query and see how it binds the data.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>