How to pass a list to a query at runtime?

[babycode]

I have a query that returns requests with CLIID and USUID. I want to take the list of CLIID and USUID and use it in two other distinct queries with this list of CLIID and USUID as a parameter in a query like

ZQuery.SQL.Text := 'SELECT CLIID, CLINAME FROM CLIENTES WHERE CLIID IN (LIST OF CLIID)'

I'm using ZeosLib with Oracle database. How to pass a list to a query at runtime?

[varianus]

AFAIK Oracle does not support parameters with the "IN" operator.

One solution could be combining first and second queries

Code: SQL  [Select][+][-]

1. SELECT CLIID, CLINAME FROM CLIENTES WHERE CLIID IN (SELECT CLIID FROM <yourfirstquery>)

Another one could be using a trick like this (I've no access to Oracle at the moment, not tested)

Code: SQL  [Select][+][-]

1. WITH CLIIDLIST (
2.                   SELECT TRIM(substr(text, instr(text, sep, 1, LEVEL) + 1,
3.                                               instr(text, sep, 1, LEVEL + 1) -
4.                                            instr(text, sep, 1, LEVEL) - 1)) AS CLIID
5.                     FROM (SELECT sep, sep || :YOURPARAMETER || sep AS text
6.                             FROM (SELECT ',' AS sep FROM dual))
7.                    CONNECT BY LEVEL <= LENGTH(text) - LENGTH(REPLACE(text, sep, '')) - 1  )
8.  
9. SELECT CLIID, CLINAME FROM CLIENTES WHERE CLIID IN (SELECT CLIID FROM CLIIDLIST)

You pass the parameter "YOURPARAMETER" as a comma separated list of codes, Oracle split it in some kind of table in memory and then you can use it in your query to get the values.

[babycode]

Thanks for responding, I implemented the first solution (subselect) and had a good result. Thanks.