CODEWITHSUNDEEP
sqloracle-databaseplsql
Sushant
Sushant

Reputation: 21

How to use a dynamic variable created using INTO within a new query using IN clause under where condition in PL/SQL

DECLARE
l_filter varchar2(100);

BEGIN
   SELECT filter INTO l_filter
   FROM dashboard
   WHERE id=TARGET_ID_1;

I get l_filter as ('016','018','011','014') from this. Now i want to use this l_filter values within IN clause in the query below dynamically.

select a,b from grid
where filter_value in l_filter;

How can I do this in PL/SQL?

Upvotes: 0

Views: 77

Answers (3)

Littlefoot
Littlefoot

Reputation: 142705

Here's how I understood the question.

Sample data first:

SQL> select * from dashboard;

        ID FILTER
---------- -----------------------
         1 '016','018','011','014'
         2 '111', '222'

SQL> select * from grid;

         A          B FIL
---------- ---------- ---
       100        200 016
       101        201 011
       200        400 xxx

SQL>

Function that returns refcursor; the key (in this example) is to split filter values into rows so that you could use them as a subquery (lines #15 - 18):

SQL> create or replace function f_get (target_id_1 in dashboard.id%type)
  2    return sys_refcursor
  3  is
  4    l_filter    varchar2(100);
  5    rc          sys_refcursor;
  6  begin
  7    select filter
  8      into l_filter
  9      from dashboard
 10      where id = target_id_1;
 11
 12    open rc for
 13      select g.a, g.b
 14      from grid g
 15      where g.filter_value in (select regexp_substr(replace(l_filter, chr(39), ''), '[^,]+', 1, level)
 16                               from dual
 17                               connect by level <= regexp_count(l_filter, ',') + 1
 18                              );
 19
 20    return rc;
 21  end;
 22  /

Function created.

Testing:

SQL> select f_get(1) from dual;

F_GET(1)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

         A          B
---------- ----------
       100        200
       101        201


SQL>

Upvotes: 0

MT0
MT0

Reputation: 167832

Use LIKE:

DECLARE
   l_filter varchar2(100);
   cur SYS_REFCURSOR;
   a GRID.A%TYPE;
   b GRID.B%TYPE;
BEGIN
   SELECT filter
   INTO   l_filter
   FROM   dashboard
   WHERE  id = TARGET_ID_1;

   OPEN cur FOR
   SELECT a,b
   FROM   grid
   WHERE  ','||SUBSTR(l_filter,2,LENGTH(l_filter)-2)||',' LIKE '%,'''||filter_value||''',%';
   
   LOOP
      FETCH cur INTO a,b;
      EXIT WHEN cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE(a || ', ' || b);
   END LOOP;
   CLOSE cur;
END;
/

db<>fiddle here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

You could use regular expressions:

select g.a, g.b
from grid g
where exists (select 1
              from dashboard d.
              where d.id = TARGET_ID_1 and
                    regexp_like(d.filter_value, '^(' || replace(d.filter, '''', ''), ',', '|') || ')$')
             );

This is not particularly efficient, but it does not require any PL/SQL or dynamic SQL.

That said, you might want to store your "filters" as rows in a table so you can just join things together, dispensing with the complication.

Upvotes: 1

Related Questions