Hi Experts,

We are using webMethods 8.2 version with JDBC Adapter 6.5 version and our database is Teradata.
I need to use IN operator in my JDBC Adapter service since I need to make a search based on a list of values.
I have successfully ran and tested the sql lstatement in Teradata SQL Assistant but when same sql is copied in custom adapter service, its not working.

Below mentioned are two sql statements that i tried to use in custom adapter but only 1st one is working and only with one value. I need to pass multiple values as input.

1. Select * from ABC where empID in (?)
2. Select * from ABC where empID in (${empID})

Second statement is not working but when error is thrown correct SQL can be seen in error details that we can execute in SQL Assistant.
e.g. Select * from ABC where empID in (11,22)

Error in Second SQL: Parameter index value 1 is outside the valid range of 1 through 0

Can we pass a list in where clause of adapter service.

Please suggest.

Thanks,
Amit

Hi Amit,

You need to use Dynamic SQL and not Custom SQL. I had this same problem ages back.

Cheers,
David

Hi Amit,

Better you can go with dynamic sql if your passing multiple values as input in where clause. Or If your input list is fixed no of fields you can use custom sql by specifying “inputjdbcType” for each field.

Please refer below link

Thanks,
Sai.

Does anyone know why we can’t specify an IN clause in a normal select/update/delete, just passing in an object list?

IN clause should work in custom and dynamic SQL template…what is the type you are using?

HTH,
RMG

Agreed, you can do it with custom and dynamic. I was wondering about a normal select/update/delete, just passing in an object list.

I don’t think it works when you pass it an argument as object list for the normal select in the IN clause…may be others can chime in.

HTH,
RMG