TeraData API Lookup based on a flat file

[samit_9999]

Hi,

I have a flat file with EmpNo and DeptNo. I am doing a lookup on teradata API based on EmpNo and try to get salary. The API has the following sql

Select EmpNo
Salary
from Employee
where EmpNo = :EmpNo

The lookup however fails with a message Cannot use value (or macro parameter) to match EmpNo

What could be the problem ?
Cant we do Teradata lookup as we OCI Lookups?

Thanks much in advance.

Sam

[chulett]

Did the stage generate that SQL or did you? I would think your parameter marker would look more like :1 or ? rather than being bound using a column name.

[Krazykoolrohit]

BTW how are you passing the employee nr to the sql?? if you ask me i would be using a job parameter and it will work fine.

[samit_9999]

Its a user defined sql.

To explain once again..

The flat file suppose gets an employee no 20. I then lookup to teradata running the following query, and try to get the Salary back.

Select EmpNo ,
Salary
from Employee
where EmpNo = :EmpNo

I also tried substituting :1 but it didnt work.

Sam

[mcolen]

If you pass the value to a teradata api filling in on stage tab teradata director(server) UserId and password for logging in to teradata. give it the table name and user defined sql to select the record using (passed field(field type) and a select from where field = :(using field name) it should be ok

[samit_9999]

Hi mcolen,

I am doing exactly the same.

Please refer my sql above...

I have passed all the other parameters as required like to userid id , password , server etc....
I have also mentioned that i am using user defined sql option.

Sam

[chulett]

Have you used User Defined SQL to do lookups with the Teradata stage before this? If not or you're not completely sure what the sql should look like - let the stage generate it for you first. Get it close and see what the generated sql looks like. Switch it User Defined and tweak from there. Then there won't be any questions around what it needs to look like or the nuances of the bindings.