62 积分	0 好友	8 主题

发消息

[SQL调优] 11203 ADG上执行sql报ORA-00976,主库上执行没有问题

1^#

发表于 2013-4-9 11:08:43 | 查看: 6083| 回复: 3

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0    Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL语句在dataguard环境执行的时候报错：ORA-00976: 此处不允许指定的伪列或运算符。错误应该出在在SQL语句标红色的部分。
SQL语句：
select tcm.policy_id,
   tcm.policy_code,
   tcm.apply_code,
   tpl.product_id,
   tpl.product_name,
   tpl.product_abbr,
   tc.company_id as customer_id,
   tc.company_name as real_name,
   tc.id_no as certi_code,
   ta.agent_id,
   ta.real_name as agent_name,
   tco.organ_id,
   tco.company_name as organ_name,
   1 as policy_year,
   1 as policy_period,
   tpm.mode_id as pay_mode,
   tpm.mode_name as pay_name,
   (select cp.initial_type
      from t_contract_product cp
      where cp.policy_id = tcp.policy_id
         and tcp.product_id = cp.product_id
         and tcp.product_num = cp.product_num
         and rownum = 1) as charge_type,
   (select cm.charge_name
      from t_contract_product cp, t_charge_mode cm
      where cp.policy_id = tcp.policy_id
         and tcp.product_id = cp.product_id
         and cm.charge_type = cp.initial_type
         and tcp.product_num = cp.product_num
         and rownum = 1) as charge_name,
   ((select min(tcf.check_enter_time)
         from t_policy_fee tcf
      where tcf.fee_type = 11
         and tcf.fee_status = 1
         and tcf.policy_id = tcm.policy_id)) as pay_date,
   tcm.validate_date as pay_start_date,
   ((select min(p.due_time)
         from t_policy_prem p
      where p.fee_type = 24
         and p.policy_id = tcm.policy_id) - 1) as pay_end_date,
   tipm.print_time as printtime,
   (select cp.charge_period
      from t_contract_product cp
      where cp.policy_id = tcp.policy_id
         and tcp.product_id = cp.product_id
         and tcp.product_num = cp.product_num
         and rownum = 1) as charge_period,
   (select max(end_date)
      from (select end_date as end_date
               from (select t.end_date, t.insured_1
                        from t_contract_product t, t_contract_master cm
                     where t.master_id is null
                        and t.prem_status = 2
                        and t.policy_id = cm.policy_id
                        and cm.policy_code = '410000016898090'
                     group by t.end_date, t.insured_1
                     order by end_date desc)
               where rownum < 2
            union
            select end_date as end_date
               from (select t.pay_to_date - 1 as end_date
                        from t_contract_product t, t_contract_master cm
                     where t.prem_status <> 2
                        and t.policy_id = cm.policy_id
                        and cm.policy_code = '410000016898090'
                        and not exists
                     (select 1
                              from t_contract_product it,
                                    t_contract_master cm2
                              where it.master_id is null
                                 and it.prem_status = 2
                                 and it.policy_id = cm2.policy_id
                                 and cm2.policy_code = '410000016898090'
                                 and it.insured_1 = t.insured_1
                                 and it.policy_id = t.policy_id)
                     order by end_date desc)
               where rownum < 2)) as end_date,
   ((select nvl(sum(t.fee_amount), 0)
         from t_policy_fee t
      where t.policy_id = tcm.policy_id
         and t.fee_type = 41
         and t.fee_status = 1) -
   (select nvl(sum(tpf.fee_amount), 0)
         from t_policy_fee tpf
      where tpf.change_id in
            (select tpc.change_id
               from t_policy_change tpc
               where tpc.service_id = 223
                  and tpc.policy_id = tcm.policy_id)
         and tpf.policy_id = tcm.policy_id
         and tpf.fee_status = 1
         and tpf.fee_type = 42)) as invocie_amount,
   tipm.invoice_print_id,
   tipm.invoice_code,
   tipm.invoice_id
  from t_contract_master tcm,
   t_group_product tcp,
   t_product_life tpl,
   t_company_customer tc,
   t_agent ta,
   t_company_organ tco,
   t_pay_mode tpm,
   t_policy_fee pp
  left join t_invoice_print_main tipm
on pp.policy_id = tipm.policy_id
and tipm.organ_id in
   (select co2.organ_id
      from t_company_organ co2
      start with co2.organ_id =
                  (select co.organ_id
                     from t_company_organ co
                  where co.class_id = 2
                  start with co.organ_id = '1411400'
                  connect by prior co.parent_id = co.organ_id)
      connect by prior co2.organ_id = co2.parent_id
      union
      select co3.organ_id from t_company_organ co3 where co3.class_id = 1)
and tipm.print_tache = 2
and tipm.annul_stauts in (1, 2, 3)
where tcm.policy_code = '410000016898090'
and tcp.policy_id = tcm.policy_id
and tpl.product_id = tcp.product_id
and tc.company_id = tcm.company_id
and ta.agent_id = tcm.agent_id
and tco.organ_id = tcm.organ_id
and tpm.mode_id = pp.pay_mode
and tcp.product_num = 100
and tcp.master_id is null
and pp.fee_type = 11
and pp.policy_id = tcm.policy_id
and pp.fee_status = 1
and exists (select 1
      from t_policy_fee pf
      where pf.fee_status = 1
         and pf.fee_type = 11
         and pf.policy_id = tcm.policy_id)
and rownum < 2

分享0