ORA-00918: 未明确定义列

Question

select t3.agent_id,
       t3.agent_name,
       t3.county_id,
       t3.dept_name,
       t3.reg_id,
       t3.reg_name,
       t3.cd_23g1 as cd_23g,
       t3.cm_23g1 as cm_23g,
       t3.pre_23g1 as pre_23g,
       round(t3.per_23g1, 3) * 100 || '%' as per_23g,
       t3.环比排名_23g1 as 环比排名_23g,
       t1.cd_2g1 as cd_2g,
       t1.cm_2g1 as cm_2g,
       t1.pre_2g1 as pre_2g,
       round(t1.per_2g1, 3) * 100 || '%' as per_2g,
       t1.环比排名_2g1 as 环比排名_2g,
       t2.cd_3g1 as cd_3g,
       t2.cm_3g1 as cm_3g,
       t2.pre_3g1 as pre_3g,
       round(t2.per_3g1, 3) * 100 || '%' as per_3g,
       t2.环比排名_3g1 as 环比排名_3g
  from (select t4.*, rownum as 环比排名_23g1
          from (select a.agent_id,
                       a.agent_name,
                       a.county_id,
                       a.dept_name,
                       a.reg_id,
                       a.reg_name,
                       sum(a.cd_23g) cd_23g1,
                       sum(a.cm_23g) cm_23g1,
                       sum(a.pre_23g) pre_23g1,
                       case
                         when sum(a.pre_23g) = 0 then
                          sum(a.cm_23g)
                         else
                          round((sum(a.cm_23g) - sum(a.pre_23g)) /
                                sum(a.pre_23g),
                                4)
                       end per_23g1,
                       a.sdate,
                       a.innetday
                  from ymk_g_a_create_report_agent a
                 where to_char(to_date(a.sdate, 'yyyy-mm'), 'yyyy-mm') || '-' ||
                       a.INNETDAY = '2011-08-28'
                   and a.county_id = 'A02'
                   and a.reg_id = 'A02005'
                 group by a.county_id,
                          a.reg_id,
                          a.reg_name,
                          a.sdate,
                          a.innetday,
                          a.dept_name,
                          a.agent_id,
                          a.agent_name
                 order by per_23g1 desc) t4) t3,
       (select t.*, rownum as 环比排名_2g1
          from (select s.agent_id,
                       s.agent_name,
                       s.county_id,
                       s.dept_name,
                       s.reg_id,
                       s.reg_name,
                       sum(s.cd_2g) cd_2g1,
                       sum(s.cm_2g) cm_2g1,
                       sum(s.pre_2g) pre_2g1,
                       case
                         when sum(s.pre_2g) = 0 then
                          sum(s.cm_2g)
                         else
                          round((sum(s.cm_2g) - sum(s.pre_2g)) / sum(s.pre_2g),
                                4)
                       end per_2g1,
                       s.sdate,
                       s.innetday
                  from ymk_g_a_create_report_agent s
                 where to_char(to_date(s.sdate, 'yyyy-mm'), 'yyyy-mm') || '-' ||
                       s.INNETDAY = '2011-08-28'
                   and s.county_id = 'A02'
                   and s.reg_id = 'A02005'
                 group by s.county_id,
                          s.reg_id,
                          s.reg_name,
                          s.sdate,
                          s.innetday,
                          s.dept_name,
                          s.agent_id,
                          s.agent_name
                 order by per_2g1 desc) t) t1,
       (select t.*, rownum as 环比排名_3g1
          from (select m.agent_id,
                       m.agent_name,
                       m.county_id,
                       m.dept_name,
                       m.reg_id,
                       m.reg_name,
                       sum(m.cd_3g) cd_3g1,
                       sum(m.cm_3g) cm_3g1,
                       sum(m.pre_3g) pre_3g1,
                       case
                         when sum(m.pre_3g) = 0 then
                          sum(m.cm_3g)
                         else
                          round((sum(m.cm_3g) - sum(m.pre_3g)) / sum(m.pre_3g),
                                4)
                       end per_3g1,
                       m.sdate,
                       m.innetday
                  from ymk_g_a_create_report_agent m
                 where to_char(to_date(m.sdate, 'yyyy-mm'), 'yyyy-mm') || '-' ||
                       m.INNETDAY = '2011-08-28'
                   and m.county_id = 'A02'
                   and m.reg_id = 'A02005'
                 group by m.county_id,
                          m.reg_id,
                          m.reg_name,
                          m.sdate,
                          m.innetday,
                          m.dept_name,
                          m.agent_id,
                          m.agent_name
                 order by per_3g1 desc) t) t2
 where t3.county_id = t1.county_id(+)
   and t3.county_id = t2.county_id(+)
   and t3.sdate = t1.sdate(+)
   and t3.sdate = t2.sdate(+)
   and t3.innetday = t1.innetday(+)
   and t3.innetday = t2.innetday(+)
   and t3.reg_id = t1.reg_id(+)
   and t3.reg_id = t2.reg_id(+)
   and t3.agent_id = t1.agent_id(+)
   and t3.agent_id = t2.agent_id(+)
 order by cm_23g desc
----------------------------------------------------------------
以上是我的sql语句，在PlSql里运行正常，但是放到程序中的时候提示ORA-00918: 未明确定义列，哪位老大帮帮啊！！！！   

Answer 1

楼主的sql里怎么还有汉字？这样不符合规范的。
你把SQL放在什么程序里？C还是JAVA？

Answer 2

order by cm_23g desc  谁的cm_23g

Answer 3

你用Group by查一下有没有重复的行。。这个报错说的是有重复的行。

Answer 4

检查是不是有相同的别名了---------这个我找了半天没找到啊
程序是.net   开发环境vs2010+oracle
sql有汉字应该没事吧     在别的地方没有出错
order by cm_23g desc 是最后查询的cm_23g

Answer 5

我在plsql可以运行    排序结果应该没错？？？？？

Answer 6

有啊我说的是最后的select 的cm_23g