我有下面的查询，它不起作用
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
它不起作用，我有与上面相同的查询。我只想运行一些查询并存储到该动态表中。通过使用该动态表，我想获取一些数据。
原始数据：
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
我收到错误消息“不支持使用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
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
您已经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;
对于您的真实代码，只需删除第二个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;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句