I’ve created a Dynamic SQL Adapter with a bunch of Joins and an “IN” Clause.
The query runs well in TOAD and SQL Developer returning a row for each value specified for column 2 (IN Clause)
SELECT (bunch of columns)
FROM (bunch of tables)
WHERE (quite a few join statements)
AND column1 = ?
AND column2 IN (?)
This query runs well, in the adapter, for only 1 value specified for the second variable (column2 - IN Clause) , but, when I try more than one value it returns zero rows.
I’ve tried enclosing each separate value for the second variable in:
no quotes-comma separated: 2739,2740,4096
no quotes-space separated: 2739 2740 4096
no quotes-comma and space separated: 2739, 2740, 4096
single quotes-comma separated: ‘2739’,‘2740’,‘4096’
single quotes-space separated: ‘2739’ ‘2740’ ‘4096’
single quotes-comma and space separated: ‘2739’, ‘2740’, ‘4096’
double quotes: “2739”, “2740”, “4096”
But none of these work and return any rows at all.
Can anyone help me with the format/syntax of the value to pass to the adapter for the second variable (IN Clause) ??
Hi Reamon,
I find that a bit odd that there’s no simple way to pass mutliple values to the variable that will be used for the “IN” clause of a Dynamic SQL. Has no-one implemented a dynamic sql utilising the “IN” clause spanning multiple values ??
Sorry. I focused in on the (?) part not on the dynamic SQL part.
Use ${in_claus} instead of (?). Set the in_claus input var of the service to your list of values. The use of ? uses bind variables. The ${var} syntax does string substitution.
Cool, but, now I’m getting an Invalid Column Index error ?? Is there any specific field delimiter I need to use within the ${var} syntax ?? For example, the ${var} contains:
AND sku.cx_domain = ‘LNAU’
AND sku.cx_sku_no IN (‘2739’,‘2740’,‘4906’)
If I remove the ${var} from the adapter, it runs and returns rows (though not correct).
With the ${var} in the adapter, it fails with Invalid Column Index ??
