This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Support WITH clause use using SET operators

jaramill
jaramill Member Posts: 4,299 Gold Trophy
edited Oct 13, 2016 5:52PM in Database Ideas - Ideas

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
jaramill
2 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    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?

    jnicholas330ApexBine
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy

    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.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    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
    jaramill Member Posts: 4,299 Gold Trophy

    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.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    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
    jaramill Member Posts: 4,299 Gold Trophy

    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.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    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.