Runtime 'IN' Condition

Hi,

I need to specify the IN condition for an SQL during runtime.
The other part of the SQL is static.

However, doing the below doesn't even seem to work.

SELECT * FROM users
WHERE login IN ('user1' || ',' || 'user2')

The string "'user1' || ',' || 'user2'" is built and set during runtime.

Anyone has any idea why it doesn't work.
Please advise.

Thanks.

Which rdbms do you use?

I had tried with Informix, DB2 and MS-SQL.
All seem to have the same problems.

In ms sql, you should use dynamic sql for that.

What is the "||" used for? In mssql, that won't mean anything, so your end result should look like this:

SELECT * FROM users
WHERE login IN ('user1', 'user2')

Hi rmiao,

Yeah, for MS-SQL we can use dynamic SQL.
But for DB like Informix, it doesn't support dynamic SQL and we have to use ESQL.

I'm developing a system which might be using different databases for each client. So, actually, I'm trying to look for a more generic solution rather than doing ESQL, etc.

For that, I wish to understand why that particular SQL won't work. If we pass 'user1' || '', it would still work (it's a run-time string as well).


Thanks.

Hi nosepicker,

I'm just trying to simplify the SQL for my posting.
For MS-SQL, it would be:

SELECT * FROM users
WHERE login IN ('user1' + ',' + 'user2')
--> user1, user2

or

SELECT * FROM users
WHERE login IN ("'user1'" + ',' + "'user2'")
==> 'user1', 'user2'

Thanks.