Look up Query with OR condition getting error ORA 1008

[ds_my_job]

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

[ray.wurlod]

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.

[chulett]

Then in transformer we are adding expression from input link which is mapped to curr2 field.
link1.curr or link1.quote_curr

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]

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

[chulett]

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"

[srinagesh]

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.

[chulett]

I doubt there is a bug at work here. This is typically a user error, caused by not understanding all the rules around building user-defined sql lookups.

[Minhajuddin]

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.