Support WITH clause use using SET operators

jaramill

Currently when writing a SQL query using Oracle's Set Operators if you have any of the queries (other than the top-most one) using the WITH clause, then Oracle will raise an exception (see below).  Not sure if this is changed in Oracle 12c but if not then another database idea for the next iteration.

ORA-32034: unsupported use of WITH clauseCause: Inproper use of WITH clause because one of the following two reasons:   1. nesting of WITH clause within WITH clause not supported yet   2. For a set query, WITH clause can't be specified for a branch.   3. WITH clause cannot be specified within parenthesis.Action: correct query and retry

Sven W.

Seems pretty easy to workaround that restriction. So I do not feel the need to change anyhing.

Can you give an example that shows why you think this would be beneficial?

jaramill

Seems pretty easy to workaround that restriction. So I do not feel the need to change anyhing.

Can you give an example that shows why you think this would be beneficial?

Well being that I know what the workaround is (make the query an in-line view), I suggested it simply because in Oracle's error message it says "unsupported" and I read that as "could be supported" in the future.  I can get by without it but would be a "nice to have".

Sven W.

Well being that I know what the workaround is (make the query an in-line view), I suggested it simply because in Oracle's error message it says "unsupported" and I read that as "could be supported" in the future.  I can get by without it but would be a "nice to have".

Using an inline view is not the only workaround. I general I do it like this and wonder why you feel the need for a WITH clause inside the union.

pseudo example

with A as (select * from TabA)      , B as (select * from TabB)     , C as (select * from TabC)select 'A' as src, id, name from AUNION ALLselect 'B' as src, id, name from BUNION ALLselect 'C' as src, id, name from C

jaramill

Because if I have ANOTHER query (using the WITH clause) and have to use a "SET"

operation such as MINUS or INTERSECT, that's when I get the error.

Sven W.

Because if I have ANOTHER query (using the WITH clause) and have to use a "SET"

operation such as MINUS or INTERSECT, that's when I get the error.

Downvoted because I still fail to see the advantage.

Example:

with A as (select 'A' txt from dual)      , B as (select 'B' txt from dual)     , C as (select 'C' txt from dual)  (select 'A' as src, txt from A  UNION ALL  select 'B' as src, txt from B  UNION ALL  select 'C' as src, txt from C  )MINUS  (  select 'C' as src, txt from C  UNION ALL  select 'B' as src, txt from B  ) ;   

Result:

SRC    TXT

A    A

jaramill

Downvoted because I still fail to see the advantage.

Example:

with A as (select 'A' txt from dual)      , B as (select 'B' txt from dual)     , C as (select 'C' txt from dual)  (select 'A' as src, txt from A  UNION ALL  select 'B' as src, txt from B  UNION ALL  select 'C' as src, txt from C  )MINUS  (  select 'C' as src, txt from C  UNION ALL  select 'B' as src, txt from B  ) ;   

Result:

SRC    TXT

A    A

I just tested this on my actual queries work and I didn't realize that I could reference any WITH clause on any OTHER query of a SET operator besides the first one.  So based on your query I tuned mine and it works.

It is when I do this (using your example but modified)

WITH A AS (SELECT 'A' AS txt FROM dual)    ,B AS (SELECT 'B' AS txt FROM dual)    ,C AS (SELECT 'C' AS txt FROM dual)SELECT 'A' AS src      ,txt  FROM A UNION ALLSELECT 'B' AS src      ,txt  FROM B UNION ALLSELECT 'C' AS src      ,txt  FROM CMINUSWITH B AS (SELECT 'B' AS txt FROM dual)    ,C AS (SELECT 'C' AS txt FROM dual)SELECT 'C' AS src      ,txt  FROM C UNION ALLSELECT 'B' AS src      ,txt  FROM B;

that I get the error that caused me to make the suggestion (though I still contend that by Oracle saying unsupported at this time that they may support that feature).

Anyway at least now I know I can use WITH in this way.

Sven W.

I just tested this on my actual queries work and I didn't realize that I could reference any WITH clause on any OTHER query of a SET operator besides the first one.  So based on your query I tuned mine and it works.

It is when I do this (using your example but modified)

WITH A AS (SELECT 'A' AS txt FROM dual)    ,B AS (SELECT 'B' AS txt FROM dual)    ,C AS (SELECT 'C' AS txt FROM dual)SELECT 'A' AS src      ,txt  FROM A UNION ALLSELECT 'B' AS src      ,txt  FROM B UNION ALLSELECT 'C' AS src      ,txt  FROM CMINUSWITH B AS (SELECT 'B' AS txt FROM dual)    ,C AS (SELECT 'C' AS txt FROM dual)SELECT 'C' AS src      ,txt  FROM C UNION ALLSELECT 'B' AS src      ,txt  FROM B;

that I get the error that caused me to make the suggestion (though I still contend that by Oracle saying unsupported at this time that they may support that feature).

Anyway at least now I know I can use WITH in this way.

It is not that I can't see that one might encounter cases where this restriction is hit. However I also see that WITH has several advantages. But most of those advantages go away if the WITH clause is used as you intent it to use.

WITH clause allows us to write and read queries from top to bottom as oposed to from inside to outside.

An example.

In the old times we wrote a query. Then put another select around this, then put another select around the second one.

select *from (select c.mon, C.minv, c.maxv         from (select trunc(B.createddate) as mon, min(a.Colval) minv, max(a.colval) maxv                   from TabA A                   join TabB B on A.ID=B.A_ID                  group by trunc(B.createddate)) C         where to_char(c.mon,'MM') = '02'         ) Dwhere d.mon>= date '2000-01-01';

This was ofter referred to as inline views.

Such a structure makes is complex to understand and to test. If the query gets longer and more complex it is extremly dificult to understand which parts of the query belong togeather, especially which WHERE clause belongs to which FROM and so on. Using good aliases certainly helps but it is not enough.

Here comes the the WITH clause.

with C as ( select trunc(B.createddate) as mon, min(a.Colval) minv, max(a.colval) maxv                   from TabA A                   join TabB B on A.ID=B.A_ID                  group by trunc(B.createddate))      ,D as (select c.mon, c.minv, c.maxv                 from C                 to_char(c.mon,'MM') = '02')select * from Dwhere d.mon>= date '2000-01-01'

We can read the query from top to bottom. The structure will not indent any further to the right just to see the hierarchy/order.

And if we want to test just the first part, all we have to do is to take this WITH part and do a select * from C on it.

This advantage goes away if we go back to the "old behaviour" and use inline views again.