Recent

Author Topic: How to pass a list to a query at runtime?  (Read 728 times)

babycode

  • New Member
  • *
  • Posts: 38
How to pass a list to a query at runtime?
« on: July 19, 2024, 03:56:26 pm »
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

  • New Member
  • *
  • Posts: 24
Re: How to pass a list to a query at runtime?
« Reply #1 on: July 20, 2024, 11:31:18 am »
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

  • New Member
  • *
  • Posts: 38
Re: How to pass a list to a query at runtime?
« Reply #2 on: July 20, 2024, 10:52:22 pm »
Thanks for responding, I implemented the first solution (subselect) and had a good result. Thanks. :)

 

TinyPortal © 2005-2018