Issue Details (XML | Word | Printable)

Key: CORE-3460
Type: Improvement Improvement
Status: Open Open
Priority: Trivial Trivial
Assignee: Unassigned
Reporter: Maxim Kuzmin
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

IN with string, contained multiple values

Created: 03/May/11 06:02 AM   Updated: 24/May/11 04:05 PM
Component/s: None
Affects Version/s: None
Fix Version/s: None

Issue Links:
Relate
 


 Description  « Hide
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).

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 03/May/11 06:18 AM
Unlikely to be accepted, see CORE-3155.

Kjell Rilbe added a comment - 04/May/11 08:43 AM
> 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.

Maxim Kuzmin added a comment - 05/May/11 11:21 AM - edited
> 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.