Union results from subselect

Question

I have an SQL statement with following structure:

f(string parameter)=select * from table(parameter) ...
=> results in some table with rows.

f(string parameter) is used as shortcut for more complex sql subquery.

table parameters:

|---params----|
   param1
   param2
   param3
   ....

how to combine f() with the table parameters where f() will be called for each param in this table. My question refers to SQL statement itself. As result I expect

f(param1)
  union all
f(param2)
  union all
f(param3)
...

If someone is interested what is staying in f() refer to my previous question answer https://stackoverflow.com/a/27599637/2023524.

Can't you just include those parameter as `OR` condition? You can probably as well use CTE. — Rahul, Dec 22 '14 at 12:13
The number of elements in table `parameters` is not defined. — Tony, Dec 22 '14 at 12:17

psaraj12 · Answer 1 · 2014-12-23T12:41:55.693

You can define f as a function like the below

create or replace function f(param IN VARCHAR2) return varchar2 is
  Result varchar2(32767);
begin
  with names as(
    SELECT REGEXP_SUBSTR(regexp_replace(replace(param,
                                                chr(13) || chr(10),
                                                ','),
                                        '[[:space:]]*',
                                        ''),
                         '[^=]+',
                         1,
                         level) as name
      FROM DUAL
    CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(param, '=') FROM DUAL)

     )

      select LISTAGG(nvl(regexp_substr(name, '[^,]+', 1, 2),
                         regexp_substr(name, '[^,]+', 1)),
                     ',') WITHIN
       GROUP(
       ORDER BY name)
        INTO Result
        from names;

  return(Result);
end f;

Then you can call your parameters like the below

with parameter(param) as (
    select 'aa = black' ||chr(13)||chr(10)||'bb = change'||chr(13)||chr(10)||'mmmm=no'  
    from dual union  all
   select 'aa = black' ||chr(13)||chr(10)||'bb = change'||chr(13)||chr(10)||'kkk=no' from dual
    )
    select distinct regexp_substr(f(param),'[^,]+',1,level) from parameter
    connect by level <=regexp_count(f(param),',')+1;

Update1:-Just for your information You can call a function within an Anonymous Block like the below

DECLARE
  function f(param IN VARCHAR2) return varchar2 is
    Result varchar2(32767);
  begin
    with names as(
      SELECT REGEXP_SUBSTR(regexp_replace(replace(param,
                                                  chr(13) || chr(10),
                                                  ','),
                                          '[[:space:]]*',
                                          ''),
                           '[^=]+',
                           1,
                           level) as name
        FROM DUAL
      CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(param, '=') FROM DUAL)

       )

        select LISTAGG(nvl(regexp_substr(name, '[^,]+', 1, 2),
                           regexp_substr(name, '[^,]+', 1)),
                       ',') WITHIN
         GROUP(
         ORDER BY name)
          INTO Result
          from names;


    return(Result);
  end f;
begin
  for i in 1 .. (regexp_count(f('aa = black' || chr(13) || chr(10) ||
                                'bb = change' || chr(13) || chr(10) ||
                                'kkk=no'),
                              ',') + 1) loop
    dbms_output.put_line(regexp_substr(f('aa = black' || chr(13) ||
                                         chr(10) || 'bb = change' ||
                                         chr(13) || chr(10) || 'kkk=no'),
                                       '[^,]+',
                                       1,
                                       i));
  end loop;
end;

unfortunately I could not test your function because I don't permit to create new functions in my db. — Tony, Dec 23 '14 at 12:08

score 0 · Answer 2 · answered Dec 22 '14 at 12:15

0

In Oracle 11g, I think you can just do this in the from clause:

with params as (
      select param1 as param from dual union all
      select param2 union all
      select param3
     )
select *
from params p,
     table(f(p.param)) f;

answered Dec 22 '14 at 12:15

Gordon Linoff

1,242,037
58
646
786

Thank you for your answer, but unfortunately I get `ORA-00904 invalid identifier` if I change parameter to p.param. As `f` is only a shortcut to some sql statement and will be expanded in from clause. – Tony Dec 22 '14 at 12:48
@Tony . . . I don't understand. Is your question about a subquery or about a table function? – Gordon Linoff Dec 22 '14 at 15:22
The question was about subquery. I've used this ambiguous notation to simplify the explanation. But I'm thankful for every information I get. I've learnt very much about sql and databases from your answers. – Tony Dec 22 '14 at 15:46

Union results from subselect

2 Answers2

Linked