My original goal (coming from a problem about splitting character-separated strings) was to use a recursive subquery in a LATERAL clause. This led me to the following finding.
It seems the WITH clause does not work in LATERAL and in CROSS APPLY. This has nothing to do with recursive WITH; no WITH clause of any kind seems to work in LATERAL/CROSS APPLY.
Is this a known restriction? I checked the documentation and I didn't see it.
Or does it actually work, and I am simply not using the correct syntax?
The documented restrictions are here:
https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702
- search for "lateral" on this page to find the LATERAL section, then read the Restrictions on LATERAL subsection. No mention that WITH clause is not permitted.
For some background and a testable case:
Note that the documentation says (in the same place):
LATERAL Specify LATERAL to designate subquery as a lateral inline view. Within a lateral inline view, you can specify tables that appear to the left of the lateral inline view in the FROM clause of a query. You can specify this left correlation anywhere within subquery (such as the SELECT, FROM, and WHERE clauses) and at any nesting level.
Notice the last five words in particular, highlighted. Indeed, experimentation shows that that works in a sub-subquery; it just doesn't work with a WITH clause. The error thrown (see below) refers specifically to the column name not being visible to the WITH clause. But the issue is not the nesting level (as we may be familiar with, from Oracle 10.2 or 11.2) - it is specifically the WITH clause.
For testing - suppose we want to select EMPNO and a flag (Y/N) for employees in the EMP table, based on whether they do or do not receive a commission. Of course this can be done in trivial ways, but I use it to illustrate LATERAL and subqueries within LATERAL.
A plain LATERAL clause works (first query). A sub-subquery within the LATERAL clause works also (second query). However, a WITH clause in LATERAL throws an error - columns from the left table are not visible to the WITH clause.
I also tried the same with CROSS APPLY instead of LATERAL; I got exactly the same results.
SQL> select e.empno, l.comm_flag
2 from scott.emp e,
3 lateral ( select case when e.comm is null then 'N' else 'Y' end as comm_flag
4 from dual
5 ) l
6 where rownum = 1
7 ;
EMPNO C
----------------------------------------- -
7369 N
SQL> select e.empno, l.comm_flag
2 from scott.emp e,
3 lateral ( select helper.comm_flag
4 from ( -- SUB-SUBQUERY. It does work!
5 select case when e.comm is null then 'N' else 'Y' end as comm_flag
6 from dual
7 ) helper
8 ) l
9 where rownum = 1
10 ;
EMPNO C
----------------------------------------- -
7369 N
SQL> select e.empno, l.comm_flag
2 from scott.emp e,
3 lateral ( with helper as ( select case when e.comm is null then 'N' else 'Y' end as comm_flag
4 from dual
5 )
6 select comm_flag from helper
7 ) l
8 ;
lateral ( with helper as ( select case when e.comm is null then 'N' else 'Y' end as comm_flag
*
ERROR at line 3:
ORA-00904: "E"."COMM": invalid identifier