Re: Query: Pro*C SELECT ... IN ... stmt

In article <3431_at_cvbnetPrime.COM>, aperez_at_caribe.prime.com (Arturo Perez x6739) writes:
>
> EXEC SQL SELECT COUNT(*)
> INTO :count
> FROM USER_COMMANDS
> WHERE USER_ID = :user_id
> AND COMMAND_NAME IN :commands;

The above query is equivalent to executing the following in sqlplus

SELECT COUNT(*) FROM USER_COMMANDS WHERE USER_ID = some_user_id   AND COMMAND_NAME IN some_command;

and is not the same as executing

SELECT COUNT(*) FROM USER_COMMANDS WHERE USER_ID = some_user_id   AND COMMAND_NAME IN (some_command_1, some_command_2, ...);

If you want to execute the above using embedded sql then you must either use that many bind variables i.e.

EXEC SQL SELECT COUNT(*) INTO :count FROM USER_COMMANDS  WHERE USER_ID = :user_id AND COMMAND_NAME IN (:command1, :command2 ...);

The above puts a limit on the number of bind variables that you can have, but if that will vary then either use dynamic method 3 or dynamic method 4.

E.g.

strcpy (somevar.arr, "SELECT COUNT(*) FROM USER_COMMANDS WHERE USER_ID \ = :user_id AND COMMAND_NAME IN ");
strcat (somevar.arr, commands);
somevar.len = strlen (somevar.arr);

EXEC SQL prepare s1 from :somevar;
EXEC SQL declare c1 cursor for s1;
EXEC SQL open c1 using :userid;
EXEC SQL FETCH C1 INTO :count;
EXEC SQL close c1;

Radhakrishna Hari
rhari_at_us.oracle.com Received on Wed Jul 01 1992 - 18:14:05 CEST