You Asked

How to transform a with statement into a pipeline function?

I have a with statement with n parts. I have to reuse the parts in other sql statements.

In order to avoid rewriting the parts. I transform the parts in pipeline functions.
It means that I have to 2 type pro part: a record and a table of this record.

In my with statement, part is generally defined like this:

partN as(
   select parNminus1.* , t.f1.....t.fk
   from t join partNminus1 on .........
)

I don't want to rewrite all the field of partNminus for the record of partN.

If I use a object, not a record, I can defined that

type o_partn is object (
     partnminus1 o_partnMinus1,
     f1          typeoff1
     ...............   
)

If it works I would like to select each field of partLastN

select t.*,t.partLastminus1.*,t.partLAstMinus2.*
from f_partLast

But the object are print and I don't want that.

and Connor said...

How about a SQL macro for this?

--
-- original
--
SQL> with my_query as
  2  ( select e.*, d.dname
  3    from scott.emp e,
  4         scott.dept d
  5    where e.deptno = d.deptno)
  6  select * from my_query;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 ACCOUNTING
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 ACCOUNTING
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 ACCOUNTING
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 RESEARCH
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 RESEARCH
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20 RESEARCH
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 RESEARCH
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20 RESEARCH
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 SALES
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30 SALES
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 SALES
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 SALES
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 SALES
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 SALES

14 rows selected.

--
-- as macro
--
SQL> CREATE OR REPLACE
  2  FUNCTION my_query_pipe return varchar2 SQL_MACRO is
  3  BEGIN
  4    RETURN q'{
  5     select e.*, d.dname
  6       from scott.emp e,
  7            scott.dept d
  8    where e.deptno = d.deptno
  9    }';
 10  END;
 11  /

Function created.

SQL> select *
  2  from my_query_pipe();

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 ACCOUNTING
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 ACCOUNTING
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 ACCOUNTING
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 RESEARCH
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 RESEARCH
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20 RESEARCH
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 RESEARCH
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20 RESEARCH
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 SALES
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500                    30 SALES
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 SALES
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 SALES
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 SALES
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 SALES

14 rows selected.

SQL>

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

OP wants it to work in a WITH clause !

Stew Ashton, November 07, 2022 - 8:23 am UTC

Behold, the most frustrating (albeit understandable) limitation of SQL macros: not allowing the refactoring of named subqueries.

SQL> CREATE OR REPLACE
  2  FUNCTION with_refactored return varchar2 SQL_MACRO is
  3  BEGIN
  4    RETURN 'select 1 n from dual';
  5  END;
  6  /

Function WITH_REFACTORED compiled

SQL> with data as (
  2    select * from with_refactored()
  3  )
  4  select * from data;

Error starting at line : 9 in command -
with data as (
  select * from with_refactored()
)
select * from data
Error at Command Line : 9 Column : 1
Error report -
SQL Error: ORA-64630: unsupported use of SQL macro: use of SQL macro inside WITH clause is not supported

A reader, November 07, 2022 - 9:30 am UTC

https://dbfiddle.uk/Yn-QoLOr

CREATE OR REPLACE
   FUNCTION my_query_pipe return varchar2 SQL_MACRO is
   BEGIN
      RETURN q'{
          select 1 from dual
      }';
  END;

select * from my_query_pipe()

with data as (
     select * from my_query_pipe()
    )
    select * from data;

with data as (
      select /*+ WITH_PLSQL */ * from my_query_pipe()
    )
    select * /*+ WITH_PLSQL */  from data;

As somebody has said in the comment it doesn't work inside a with statement..

My version of Oracle is not advanced enought to use macro unfortunately.

I would rather use a pipeline function because I have a easy and clear way to see what fields are returned. (see the definction of the record corresponding to the pipelined function).

Thanks for your answer but I would be glad if you had a better solution

If I understand correctly...

Stew Ashton, November 08, 2022 - 9:53 am UTC

You can only ask for "*" if you are referring to a table/view/subquery/nested table. Instead of returning a row containing an object, return a one-row table of that object.

create or replace type t_d_e force as object(
  deptno number(2,0),
  dname varchar2(14),
  ename varchar2(10),
  job varchar2(10)
)
/
create or replace type tt_d_e force as table of t_d_e
/
with de10 as (
  select tt_d_e(t_d_e(deptno, dname, ename, job)) d_e10 
  from dept join emp using(deptno) 
  where deptno = 10 and rownum = 1
)
, de20 as  (
  select tt_d_e(t_d_e(deptno, dname, ename, job)) d_e20 
  from dept join emp using(deptno) 
  where deptno = 20 and rownum = 1
)
, de30 as  (
  select tt_d_e(t_d_e(deptno, dname, ename, job)) d_e30 
  from dept join emp using(deptno) 
  where deptno = 30 and rownum = 1
)
select data10.*, data20.*, data30.*
from de10, table(d_e10) data10,
de20, table(d_e20) data20,
de30, table(d_e30) data30
/

    DEPTNO DNAME          ENAME      JOB            DEPTNO DNAME          ENAME      JOB            DEPTNO DNAME          ENAME      JOB       
---------- -------------- ---------- ---------- ---------- -------------- ---------- ---------- ---------- -------------- ---------- ----------
        10 ACCOUNTING     KING       PRESIDENT          20 RESEARCH       JONES      MANAGER            30 SALES          BLAKE      MANAGER

Or maybe this is closer to what you want?

with de10 as (
  select deptno, dname, ename, job
  from dept join emp using(deptno) 
  where deptno = 10 and rownum = 1
)
, de20 as  (
  select deptno, dname, ename, job
  from dept join emp using(deptno) 
  where deptno = 20 and rownum = 1
)
, de30 as  (
  select deptno, dname, ename, job
  from dept join emp using(deptno) 
  where deptno = 30 and rownum = 1
)
select * from (
  select * from de10 union all
  select * from de20 union all
  select * from de30
)
pivot(
  max(dname) dname, max(ename) ename, max(job) job 
  for deptno in(10 d10,20 d20,30 d30)
)

D10_DNAME      D10_ENAME  D10_JOB   D20_DNAME      D20_ENAME  D20_JOB   D30_DNAME      D30_ENAME  D30_JOB  
-------------- ---------- --------- -------------- ---------- --------- -------------- ---------- ---------
ACCOUNTING     KING       PRESIDENT RESEARCH       JONES      MANAGER   SALES          BLAKE      MANAGER

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

OP wants it to work in a WITH clause !

If I understand correctly...

More to Explore

PL/SQL demos

PL/SQL docs