【筆記】ora-00979 bug？

程式碼：

with temp as (
select aa.policy_id, count(distinct aa.change_id)
  from t_policy_change aa, t_contract_master b
 where aa.policy_id = b.policy_id
   and aa.change_status = 3
   and aa.service_id = 123
   and b.policy_type in (1, 3)
   and b.accept_date >= date '2006-1-1'
   and b.accept_date < date '2009-1-1' having
 count(distinct aa.change_id) >= 2
 group by aa.policy_id)

select a.organ_id,
       decode(a.policy_type, 1, 'a', 3, 'b'),
       chr(11) || a.policy_code,
       aa.validate_time,
       aa.change_id,
       c.type_name,
       (select real_name from t_customer where customer_id = a.applicant_id),
       (select agent_id from t_agent where agent_id = a.service_id),
       e.real_name,
       e.agent_code,
       (select AGENT_STATUS_NAME
          from t_agent_status
         where AGENT_STATUS = e.agent_status),
       e.dept_id
  from t_contract_master    a,
       t_policy_change      aa,
       T_SERVICE_APPLY_TYPE c,
       temp                 d,
       t_temp_change d,
       t_agent       e
 where a.policy_id = d.policy_id
   and a.policy_id = aa.policy_id
   and a.accept_date >= date '2006-1-1'
   and a.accept_date < date '2009-1-1'
   and a.policy_type in (1, 3)
   and aa.service_id = 123
   and aa.change_status = 3
   and aa.apply_type = c.apply_type(+)
   and a.agent_id = e.agent_id

報錯：ora-00979 ,在

 (select real_name from t_customer where customer_id = a.applicant_id),
 (select agent_id from t_agent where agent_id = a.service_id)

可能是這個版本在解析的時候出錯了，應該是bug，或者這個版本不支援這樣的寫法。還沒有得到證實。

select * from v$version;

1 Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
2 PL/SQL Release 9.2.0.1.0 - Production
3 CORE 9.2.0.1.0 Production
4 TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
5 NLSRTL Version 9.2.0.1.0 - Production

而在以下版本的庫中不報錯：

1 Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
2 PL/SQL Release 9.2.0.8.0 - Production
3 "CORE 9.2.0.8.0 Production"
4 TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
5 NLSRTL Version 9.2.0.8.0 - Production

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-567200/，如需轉載，請註明出處，否則將追究法律責任。