Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Getting a Nested With Clause error while creating a Table

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.
Best 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 theCREATE 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
-
Hi @User_QDHXF ,
there are a couple of errors in your query:
- In the sub select of Q2 you select from "table", which is not a valid table expression because table is a key word.
- In Q3 you reference the table alias q3 for col3 but it has to be q2.
- In Q4 the selected column names are not unique. Use column aliases to make them unique.
- 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
-
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.
-
Is this the same as
and if it is, why do you need to post the same question twice?
-
@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.
-
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.
-
@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.
-
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 theCREATE 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.
-
@mathguy Thank you. It resolved the error. Trivial mistake, indeed. Appreciate your help.