ORacle table lookup

[srekant]

Hi,
I am doing direct lookup to an oracle table (having userdefined Query with multiple unions) when i run the job with out giving bind variable "the first row value of lookup is repeated for the remaining rows".
when i use a bind variable i am getting the following error

Code: Select all

"ORA-01036: illegal variable name/number"

[chulett]

Post your query.

[rwierdsm]

I've seen this error when the input key value does not line up with ':1" value in the query, i.e. you are passing the bind variable in the 4th port, but have defined your variable as the first port (:1). Make sure they all line up, better still, ensure your key values are at the top of the list.

e.g.

Code: Select all

select x, y, z from table_1
where a = [b]:1[/b]

where the list of columns into the sql are:

column 1 (non key)
column 2 (non key)
column 3 (non key)
column 4 (key)

In this simplistic example, the SQL will be modified to be

Code: Select all

select x, y, z from table_1
where a = [b]:4[/b]

Hope this is clear?!?!?

Rob W

[chulett]

Was gonna get there when the query was posted.

Biggest thing is to ensure that all columns are 'bound', that there is a parameter marker in the query for each column defined in the stage. Also, fields marked as Keys are expected to be used in the 'where' clause but I'm not sure if that is enforced when using User Defined SQL in a reference lookup.