ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【笔记】ora-00979 bug?

【笔记】ora-00979 bug?

原创 Linux操作系统 作者:yellowlee 时间:2009-03-13 15:52:05 0 删除 编辑

代码:

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 【笔记】SQL tuning
请登录后发表评论 登录
全部评论

注册时间:2008-12-27

  • 博文量
    316
  • 访问量
    659520