Forum Stats

  • 3,759,224 Users
  • 2,251,514 Discussions
  • 7,870,541 Comments

Discussions

Getting a Nested With Clause error while creating a Table

User_QDHXF
User_QDHXF Member Posts: 20 Green Ribbon

Hello,

I am trying to create a table from a query. However I am getting the following error:

ORA-32034: unsupported use of WITH clause

32034. 00000 - "unsupported use of WITH clause"

*Cause:  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.

My current Query Structure that's throwing the above error:

With Q2 AS (

SELECT Q1.col1, Q1.col2,Q1.col3

FROM

(Select col1,col2,col3 from table) Q1

),

Q3 AS (

Select Q2.col1,Q2.col2,Q3.col3

FROM Q2

WHERE <conditions>

),

Q4 AS (

Select Q3.col1,Q3.col2,Q3.col3,Q2.col1,Q2.col2,Q3.col3

from Q3 left outer join Q2 on col1 = col1

),

select * from Q4;


Please let me know how I can change my query structure to avoid this error.


Thank you.

Tagged:

Best Answer

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond
    edited Sep 16, 2021 6:45PM Accepted Answer

    Trivial mistake.

    Remove the parentheses around QUERY POSTED ABOVE. The error message is telling you as much: WITH clause cannot be specified within parentheses. Nor do you need parentheses for the CREATE TABLE ... AS ... statement.

    Compare:

    create table emp_copy as
     (with t as (select * from scott.emp)
      select * from t);
    


    Error starting at line : 1 in command -

    create table emp_copy as

     (with t as (select * from scott.emp)

      select * from t)

    Error report -

    ORA-32034: unsupported use of WITH clause

    32034. 00000 - "unsupported use of WITH clause"

    *Cause:   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


    create table emp_copy as
       with t as (select * from scott.emp)
       select * from t;
    


    Table EMP_COPY created.

Answers

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge

    Hi @User_QDHXF ,

    there are a couple of errors in your query:

    1. In the sub select of Q2 you select from "table", which is not a valid table expression because table is a key word.
    2. In Q3 you reference the table alias q3 for col3 but it has to be q2.
    3. In Q4 the selected column names are not unique. Use column aliases to make them unique.
    4. Also in Q4 the join condition is not valid because col1 exists in q2 and also in q3. Use table aliases (q2.col1 = q3.col1)

    If the query works after all corrections the it also works in a ctas.

    Best regards

    Jan

  • User_QDHXF
    User_QDHXF Member Posts: 20 Green Ribbon

    @Jan Gorkow

    Thank you for replying. Actually I was trying to dummy my actual query structure and therefore resorted to some dummy columns and obviously there's a typo.

    Here's my actual query, that runs without any error and gives me the result. But it's not allowing me to create a Table due to With Clause error.

    WITH q2 AS (/*GET ALL COLUMNS AND HCRIS_COST_REPORT AND TOTAL_REIMBURSABLE_COST*/

      (SELECT

        q1.prvdr_id,

        q1.pcrp_start_dt,

        q1.pcrp_end_dt,

        q1.hcris_load_id,

        q1.pcrp_rownum,

        CASE

          WHEN hcris_desc.hc_mcr_typ_num IN (

            '4',

            '5'

          ) THEN 'Max'

              || ' '

              || hcris_desc.hc_mcr_typ_num_shrt_desc

          ELSE hcris_desc.hc_mcr_typ_num_shrt_desc

        END hcris_cost_report,

        SUM(hm_pvrdr.total_reimbursable_cost) total_reimbursable_cost

      FROM

        (/*GET BASELINE COLUMNS AND ROWNUM ON PCRP_END_DATE DESC*/

          SELECT

            prvdr_tab.prvdr_id,

        --prvdr_cst_tab.prvdr_seq_num,

            prvdr_cst_tab.pcrp_start_dt,

            prvdr_cst_tab.pcrp_end_dt,

            psrstar.get_max_hcris_cr_incl_pubd(prvdr_cst_tab.pcrp_id) hcris_load_id,

            ROW_NUMBER() OVER(

              PARTITION BY prvdr_tab.prvdr_id

              ORDER BY

                prvdr_cst_tab.pcrp_end_dt DESC

            ) pcrp_rownum

          FROM

            psrstar.psr_prvdr prvdr_tab

            LEFT JOIN psrstar.psr_prvdr_cst_rpt_prd prvdr_cst_tab ON prvdr_tab.prvdr_id = prvdr_cst_tab.prvdr_id

                                         AND prvdr_tab.prvdr_seq_num = prvdr_cst_tab.prvdr_seq_num

            JOIN psrstar.psr_ffy ffy ON prvdr_cst_tab.pcrp_start_dt BETWEEN ffy.ffy_start_dt AND ffy.ffy_end_dt

          WHERE

            pcrp_stus_ind <> 'I'

       -- and prvdr_tab.prvdr_id = '010134'

        ) q1

        LEFT JOIN psrstar.psr_hcris_cr hcris_num ON hcris_load_id = hcris_num.hc_ld_id

        LEFT JOIN psrstar.psr_hc_mcr_typ hcris_desc ON hcris_num.hc_mcr_typ_num = hcris_desc.hc_mcr_typ_num

        LEFT JOIN psrstrpt.v_hcris_measures_by_prvdr hm_pvrdr ON q1.hcris_load_id = hm_pvrdr.hc_ld_id

      GROUP BY

        q1.prvdr_id,

        q1.pcrp_start_dt,

        q1.pcrp_end_dt,

        q1.hcris_load_id,

        q1.pcrp_rownum,

        CASE

          WHEN hcris_desc.hc_mcr_typ_num IN (

            '4',

            '5'

          ) THEN 'Max'

              || ' '

              || hcris_desc.hc_mcr_typ_num_shrt_desc

          ELSE hcris_desc.hc_mcr_typ_num_shrt_desc

        END

      )

      ORDER BY

        q1.prvdr_id

    ), q3 AS (/*GET ALL COLUMNS WITH MAXIMUM PCRP_END_DATE WITH LOADED HCRIS_ID*/

      SELECT

        q2.prvdr_id,

    q2.pcrp_start_dt,

        q2.pcrp_end_dt,

        q2.hcris_load_id,

        q2.pcrp_rownum,

        q2.hcris_cost_report,

        q2.total_reimbursable_cost

      FROM

        q2

      WHERE

        q2.pcrp_rownum = (

          SELECT

            min(t1.pcrp_rownum)

          FROM

            q2 t1

          WHERE

            t1.hcris_load_id IS NOT NULL

            AND q2.prvdr_id = t1.prvdr_id

        )

    ), q4 AS (/*COMBINE BOTH CURRENT AND PRIOR COLUMNS*/

      SELECT

        q3.prvdr_id         kpi_prvdr_id,

        q3.hcris_load_id       current_hcris_id,

        q3.hcris_cost_report     current_cost_report,

        q3.total_reimbursable_cost  current_total_reimb_cst,

        q2.hcris_load_id       prior_hcris_id,

        q2.hcris_cost_report     prior_cost_report,

        q2.total_reimbursable_cost  prior_total_reimb_cst,

      CASE WHEN

         Round((Q2.Total_Reimbursable_Cost)/(Q2.pcrp_end_dt - (Q2.pcrp_start_dt)+1),2) = 0 THEN 'N/A'

         WHEN

         Round((Q2.Total_Reimbursable_Cost)/(Q2.pcrp_end_dt - (Q2.pcrp_start_dt)+1),2) IS NULL 

         THEN 'N/A' ELSE

        CAST(Round((((Q3.Total_Reimbursable_Cost)/(Q3.pcrp_end_dt - (Q3.pcrp_start_dt)+1))-

        (((Q2.Total_Reimbursable_Cost)/(Q2.pcrp_end_dt - (Q2.pcrp_start_dt)+1))))/

        ABS(((Q2.Total_Reimbursable_Cost)/(Q2.pcrp_end_dt - (Q2.pcrp_start_dt)+1))),0) AS VARCHAR(20)) END Reimb_grwth 

      FROM

        Q3

        LEFT OUTER JOIN Q2 ON q2.prvdr_id = q3.prvdr_id 

                   AND q2.pcrp_rownum = q3.pcrp_rownum + 1

    )

    SELECT


        q4.kpi_prvdr_id,   

        q4.current_hcris_id,

        q4.current_cost_report,

        q4.current_total_reimb_cst,

        q4.prior_hcris_id,

        q4.prior_cost_report,

        q4.prior_total_reimb_cst,

        q4.Reimb_grwth

        

    FROM

      q4

    Please advise.

  • User_QDHXF
    User_QDHXF Member Posts: 20 Green Ribbon

    @mathguy That's the same question. I had to re-post the question because the first time I did, I got an error - Cannot to find the discussion. I search on my profile to see if the question was posted and it did not show up. Therefore I had re-write the whole thing. Apologies for the confusion.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    Hi, @User_QDHXF

    Here's my actual query, that runs without any error and gives me the result. But it's not allowing me to create a Table due to With Clause error.

    So post there's no problem with the statement you posted, and you don't need any help with it; is that right?

    Then post the statement that IS causing the error.

  • User_QDHXF
    User_QDHXF Member Posts: 20 Green Ribbon

    @Frank Kulash The query that I posted executes fine, without any error. However when I try to create a table from the query above, I am getting the following error:

    ORA-32034: unsupported use of WITH clause

    32034. 00000 - "unsupported use of WITH clause"

    *Cause:  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


    I am doing a Create Table Table_TEMP

    AS (QUERY POSTED ABOVE); This gives me the mentioend With clause error.

  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond
    edited Sep 16, 2021 6:45PM Accepted Answer

    Trivial mistake.

    Remove the parentheses around QUERY POSTED ABOVE. The error message is telling you as much: WITH clause cannot be specified within parentheses. Nor do you need parentheses for the CREATE TABLE ... AS ... statement.

    Compare:

    create table emp_copy as
     (with t as (select * from scott.emp)
      select * from t);
    


    Error starting at line : 1 in command -

    create table emp_copy as

     (with t as (select * from scott.emp)

      select * from t)

    Error report -

    ORA-32034: unsupported use of WITH clause

    32034. 00000 - "unsupported use of WITH clause"

    *Cause:   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


    create table emp_copy as
       with t as (select * from scott.emp)
       select * from t;
    


    Table EMP_COPY created.

  • User_QDHXF
    User_QDHXF Member Posts: 20 Green Ribbon

    @mathguy Thank you. It resolved the error. Trivial mistake, indeed. Appreciate your help.