IN with string, contained multiple values [CORE3460]

[firebird-automations]

Submitted by: Maxim Kuzmin (cybermax)

Is related to CORE3155

When using WHERE with IN (), for condition allowed only single value (integer or varchar). When using string-condition with multiple values, it's raise exception:
SELECT FROM TABLE 1 WHERE ID IN ('1') -- OK
SELECT FROM TABLE 1 WHERE ID IN ('1, 2, 4, 6') -- EXCEPTION
Also, this behavior does not use string parameters, such as WHERE ID IN (:LIST_ID).

[firebird-automations]

Modified by: @dyemanov

Link: This issue is related to CORE3155 [ CORE3155 ]

[firebird-automations]

Commented by: @dyemanov

Unlikely to be accepted, see CORE3155.

[firebird-automations]

Commented by: @krilbe

> SELECT FROM TABLE 1 WHERE ID IN ('1, 2, 4, 6') -- EXCEPTION

Correct syntax is:
SELECT FROM TABLE 1 WHERE ID IN ('1', '2', '4', '6')

> Also, this behavior does not use string parameters, such as WHERE ID IN (:LIST_ID).

Can't do it that way. Try this:

SELECT FROM TABLE 1 WHERE ID IN (:Item1, :Item2, :Item3)

Or dynamically build your in list using string manipulation in your
client app. Or use a temporary table.

[firebird-automations]

Commented by: Maxim Kuzmin (cybermax)

> Or dynamically build your in list using string manipulation in your client app. Or use a temporary table.

Thank you, I use both methods, also a selection from special SP. But it is very inconvenient.

[commanderz]

select * from TABLE_1 T1 where position(','||T1.field_id||',',',1,2,11,120,')>0