Oracle SQL "IN" Clause - What's the proper Syntax/Format ??

, ,
1

Hi,

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) ??

Cheers,
David

2

Alas, the query is such that your list of strings is not treated as a list at all. It is treated as a single value.

Using your first try as an example: colum2 IN (“2739,2740,4096”)

Each of the variations you tried are each getting treated as though the entire string is enclosed in quotes.

See the following for additional detail. You might be stymied. :frowning:

[URL]Questions

3

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 ??

Cheers,
David

4

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.

5

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 ??

Any thoughts ??

6

Erroneous ; somewhere?

7

Odd… Tested it this morning with the bloody same predicate formatting and it’s working. Must have been something in the IS.

Thanks for your help…