Multi tool use

Dynamic table in SQL query using union

I have below query and it is not working

with mag_re as(
select a as "A", b as "B"
from table1
where 'condition'

union all

select a as "A", b as "B"
from table2
where 'condition'
)
select t.a as "A", t.b as "B", M.b as "BB"
from table3 t, mag_re M
where t.a = M.A

union all

select t.a as "A", t.b as "B", M.b as "BB"
from table4 t, mag_re M
where t.a = M.A


It is not working, I have the query same as above.
I just want to run some query and store into that dynamic table.
By using that dynamic table I want to get some data.

Original data:

with market_reg as (
select acc.account_name,
rc1.code_label
from customer_node_history cnh,account acc,schedule sc,customer_node_type cnt,reference_Code rc1
where acc.customer_node_id = cnh.customer_node_id
and cnh.customer_node_status_code = 3
and account_type_id = 10000
and sc.schedule_id = cnh.schedule_id
and cnh.Hierarchy_level = 1
and cnh.customer_node_type_id = cnt.customer_node_type_id
and sysdate between cnh.effective_start_date and cnh.effective_end_date
and rc1.reference_code = cnh.general_1
and rc1.reference_type_id = 1012882
union
select acc.account_name,
rc1.code_label
from customer_node_history cnh,account acc,schedule sc,customer_node_type cnt,reference_Code rc1,
account acc1,customer_node_history cnh1
where acc.customer_node_id = cnh.customer_node_id
and cnh.customer_node_status_code = 3
and acc.account_type_id = 10000
and sc.schedule_id = cnh.schedule_id
and cnh.Hierarchy_level != 1
and cnh.customer_node_type_id = cnt.customer_node_type_id
and sysdate between cnh.effective_start_date and cnh.effective_end_date
and cnh.root_customer_node_id = acc1.customer_node_id
and acc1.account_type_id = 10000
and sysdate between cnh1.effective_start_date and cnh1.effective_end_date
and cnh1.customer_node_id = acc1.customer_node_id
and rc1.reference_code = cnh.general_1
and rc1.reference_type_id = 1012882)
select sc.schedule_name "SCHEDULE_NAME",
acc.account_name "ACCOUNT_NAME",
cnh.node_name "NODE_NAME",
cnt.customer_node_type_name "Market Segment",cnh.Hierarchy_level, acc.account_name "ROOT_ACCOUNT_NAME",
cnh.node_name "ROOT_NODE_NAME",
mr.code_label "Market Region"
from customer_node_history cnh,account acc,schedule sc,customer_node_type cnt,market_reg mr
where acc.customer_node_id = cnh.customer_node_id
and cnh.customer_node_status_code = 3
and account_type_id = 10000
and sc.schedule_id = cnh.schedule_id
and cnh.Hierarchy_level = 1
and cnh.customer_node_type_id = cnt.customer_node_type_id
and sysdate between cnh.effective_start_date and cnh.effective_end_date
and acc.account_name = mr.ACCOUNT_NAME
union
with market_reg as (
select acc.account_name,
rc1.code_label
from customer_node_history cnh,account acc,schedule sc,customer_node_type cnt,reference_Code rc1
where acc.customer_node_id = cnh.customer_node_id
and cnh.customer_node_status_code = 3
and account_type_id = 10000
and sc.schedule_id = cnh.schedule_id
and cnh.Hierarchy_level = 1
and cnh.customer_node_type_id = cnt.customer_node_type_id
and sysdate between cnh.effective_start_date and cnh.effective_end_date
and rc1.reference_code = cnh.general_1
and rc1.reference_type_id = 1012882
union
select acc.account_name,
rc1.code_label
from customer_node_history cnh,account acc,schedule sc,customer_node_type cnt,reference_Code rc1,
account acc1,customer_node_history cnh1
where acc.customer_node_id = cnh.customer_node_id
and cnh.customer_node_status_code = 3
and acc.account_type_id = 10000
and sc.schedule_id = cnh.schedule_id
and cnh.Hierarchy_level != 1
and cnh.customer_node_type_id = cnt.customer_node_type_id
and sysdate between cnh.effective_start_date and cnh.effective_end_date
and cnh.root_customer_node_id = acc1.customer_node_id
and acc1.account_type_id = 10000
and sysdate between cnh1.effective_start_date and cnh1.effective_end_date
and cnh1.customer_node_id = acc1.customer_node_id
and rc1.reference_code = cnh.general_1
and rc1.reference_type_id = 1012882)
select sc.schedule_name "SCHEDULE_NAME",
acc.account_name "ACCOUNT_NAME",
cnh.node_name "NODE_NAME",
cnt.customer_node_type_name "Market Segment",cnh.Hierarchy_level, acc1.account_name "ROOT_ACCOUNT_NAME",
cnh1.node_name "ROOT_NODE_NAME",
mr.code_label "Market Region"
from customer_node_history cnh,account acc,schedule sc,customer_node_type cnt,
account acc1,customer_node_history cnh1,,market_reg mr
where acc.customer_node_id = cnh.customer_node_id
and cnh.customer_node_status_code = 3
and acc.account_type_id = 10000
and sc.schedule_id = cnh.schedule_id
and cnh.Hierarchy_level != 1
and cnh.customer_node_type_id = cnt.customer_node_type_id
and sysdate between cnh.effective_start_date and cnh.effective_end_date
and cnh.root_customer_node_id = acc1.customer_node_id
and acc1.account_type_id = 10000
and sysdate between cnh1.effective_start_date and cnh1.effective_end_date
and cnh1.customer_node_id = acc1.customer_node_id
and acc.account_name = mr.ACCOUNT_NAME


I get error as "unsupported use of WITH clause"

as "A"

with

1 Answer
1

The real code you posted isn't doing what your original simplified code showed. You're actually doing something more like:

with mag_re as(
select a as "A", b as "B"
from table1
where 'condition' = 'x'
union all
select a as "A", b as "B"
from table2
where 'condition' = 'x'
)
select t.a as "A", t.b as "B", M.b as "BB"
from table3 t, mag_re M
where t.a = M.A
union all
with mag_re as(
select a as "A", b as "B"
from table1
where 'condition' = 'x'
union all
select a as "A", b as "B"
from table2
where 'condition' = 'x'
)
select t.a as "A", t.b as "B", M.b as "BB"
from table4 t, mag_re M
where t.a = M.A;

SQL 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.
*Action: correct query and retry


You have repeated the with clause in each half of your main union, which is incorrect and is causing the error. Part of the point of subquery factoring is to stop you having to repeat code. So (ironically) your initial simplified code is actually fine, as the with was not repeated:

with

with

with mag_re as(
select a as "A", b as "B"
from table1
where 'condition' = 'x'
union all
select a as "A", b as "B"
from table2
where 'condition' = 'x'
)
select t.a as "A", t.b as "B", M.b as "BB"
from table3 t, mag_re M
where t.a = M.A
union all
select t.a as "A", t.b as "B", M.b as "BB"
from table4 t, mag_re M
where t.a = M.A;


For your real code just remove the second with clause:

with

with market_reg as (
select acc.account_name,
rc1.code_label
from customer_node_history cnh,account acc,schedule sc,customer_node_type cnt,reference_Code rc1
where acc.customer_node_id = cnh.customer_node_id
and cnh.customer_node_status_code = 3
and account_type_id = 10000
and sc.schedule_id = cnh.schedule_id
and cnh.Hierarchy_level = 1
and cnh.customer_node_type_id = cnt.customer_node_type_id
and sysdate between cnh.effective_start_date and cnh.effective_end_date
and rc1.reference_code = cnh.general_1
and rc1.reference_type_id = 1012882
union
select acc.account_name,
rc1.code_label
from customer_node_history cnh,account acc,schedule sc,customer_node_type cnt,reference_Code rc1,
account acc1,customer_node_history cnh1
where acc.customer_node_id = cnh.customer_node_id
and cnh.customer_node_status_code = 3
and acc.account_type_id = 10000
and sc.schedule_id = cnh.schedule_id
and cnh.Hierarchy_level != 1
and cnh.customer_node_type_id = cnt.customer_node_type_id
and sysdate between cnh.effective_start_date and cnh.effective_end_date
and cnh.root_customer_node_id = acc1.customer_node_id
and acc1.account_type_id = 10000
and sysdate between cnh1.effective_start_date and cnh1.effective_end_date
and cnh1.customer_node_id = acc1.customer_node_id
and rc1.reference_code = cnh.general_1
and rc1.reference_type_id = 1012882)
select sc.schedule_name "SCHEDULE_NAME",
acc.account_name "ACCOUNT_NAME",
cnh.node_name "NODE_NAME",
cnt.customer_node_type_name "Market Segment",cnh.Hierarchy_level, acc.account_name "ROOT_ACCOUNT_NAME",
cnh.node_name "ROOT_NODE_NAME",
mr.code_label "Market Region"
from customer_node_history cnh,account acc,schedule sc,customer_node_type cnt,market_reg mr
where acc.customer_node_id = cnh.customer_node_id
and cnh.customer_node_status_code = 3
and account_type_id = 10000
and sc.schedule_id = cnh.schedule_id
and cnh.Hierarchy_level = 1
and cnh.customer_node_type_id = cnt.customer_node_type_id
and sysdate between cnh.effective_start_date and cnh.effective_end_date
and acc.account_name = mr.ACCOUNT_NAME
union
select sc.schedule_name "SCHEDULE_NAME",
acc.account_name "ACCOUNT_NAME",
cnh.node_name "NODE_NAME",
cnt.customer_node_type_name "Market Segment",cnh.Hierarchy_level, acc1.account_name "ROOT_ACCOUNT_NAME",
cnh1.node_name "ROOT_NODE_NAME",
mr.code_label "Market Region"
from customer_node_history cnh,account acc,schedule sc,customer_node_type cnt,
account acc1,customer_node_history cnh1,,market_reg mr
where acc.customer_node_id = cnh.customer_node_id
and cnh.customer_node_status_code = 3
and acc.account_type_id = 10000
and sc.schedule_id = cnh.schedule_id
and cnh.Hierarchy_level != 1
and cnh.customer_node_type_id = cnt.customer_node_type_id
and sysdate between cnh.effective_start_date and cnh.effective_end_date
and cnh.root_customer_node_id = acc1.customer_node_id
and acc1.account_type_id = 10000
and sysdate between cnh1.effective_start_date and cnh1.effective_end_date
and cnh1.customer_node_id = acc1.customer_node_id
and acc.account_name = mr.ACCOUNT_NAME;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.