CODEWITHSUNDEEP
oracle-databasewindow-functionssql-macro
MAndreato
MAndreato

Reputation: 23

Oracle SQL_Macro and analytic functions

The following SQL Macro returns all (three) rows or just one, depending on P_ID parameter. Logic consists in basic appended records and an analytic LAG function:

create or replace function Test_SQL_Macro_Analytic_M(P_ID in integer) return clob sql_macro is
begin
  return 'select * 
            from (select ID, lag(ID, 1) over (order by ID) as Prev_ID
                    from (select 1 ID from dual union all 
                          select 2 ID from dual union all 
                          select 3 ID from dual))
           where ID = nvl(P_ID, ID)';
end Test_SQL_Macro_Analytic_M;

Example of all rows:

select * from Test_SQL_Macro_Analytic_M(P_ID => null) order by ID;
ID PREV_ID
1
2 1
3 2

Example of only one row:

select * from Test_SQL_Macro_Analytic_M(P_ID => 2) order by ID;
ID PREV_ID
2 1

But instead of passing P_ID directly, suppose it's taken from another query:

with P as (select 2 P_ID from dual) -- this could be way more complex...
select M.*
  from P, Test_SQL_Macro_Analytic_M(P_ID => P.P_ID) M
ID PREV_ID
2 1

So far, so good!

Now, I'm going to change the macro logic but ignore its details (i.e. having the LAG on a filtered one row subset is not useful...):

create or replace function Test_SQL_Macro_Analytic_M(P_ID in integer) return clob sql_macro is
begin
  return 'select ID, lag(ID, 1) over (order by ID) as Prev_ID
            from (select 1 ID from dual union all 
                  select 2 ID from dual union all 
                  select 3 ID from dual)
           where ID = nvl(P_ID, ID)';
end Test_SQL_Macro_Analytic_M;

The following is correctly returning a record (forget the logic!):

with P as (select 2 P_ID from dual)
select M.ID --,M.Prev_ID --> if you add this field, then macro returns all rows instead of filtering
  from P, Test_SQL_Macro_Analytic_M(P_ID => P.P_ID) M

The following instead returns the full set:

with P as (select 2 P_ID from dual)
select M.ID, M.Prev_ID --> if you add this field, then macro returns all rows instead of filtering
  from P, Test_SQL_Macro_Analytic_M(P_ID => P.P_ID) M

fiddle

I believe the query should always return only one record, regardless of which fields are selected.

Workaround by MT0:

with P as (select 2 P_ID from dual)
select M.ID, M.Prev_ID
  from P
cross join lateral (select * from Test_SQL_Macro_Analytic_M(P_ID => P.P_ID)) M

Upvotes: 2

Views: 86

Answers (1)

p3consulting
p3consulting

Reputation: 4595

Work around: wrap the macro in LATERAL

lateral( select * from Test_SQL_Macro_Analytic_M(PID => P.P_ID)) M

Upvotes: 2

Related Questions