Getting a Nested With Clause error while creating a Table

User_QDHXF

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.

mathguy

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.

Jan Gorkow

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

@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.

mathguy

Is this the same as

https://community.oracle.com/tech/developers/discussion/4489093/getting-a-improper-with-clause-error-while-creaeting-a-table#latest

and if it is, why do you need to post the same question twice?

User_QDHXF

@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

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

@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

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

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