Code Bye

ibatis执行sql报:未明确定义列

SELECT *
  FROM (SELECT page.*, ROWNUM AS rn
          FROM ((select p.id,
                        p.plate,
                        p.device_id,
                        p.platecolor_code,
                        p.platetype_code,
                        p.cartype_code,
                        p.carcolor_code,
                        p.catchspeed,
                        p.catchtime,
                        ty.code,
                        ty.name,
                        e.resource_id,
                        e.type,
                        re.local,
                        re.remote,
                        re.absolute,
                        r.name,
                        rd.name,
                        rd.lane_number
                   from EA_ILLEGAL_PASS p
                   left join EA_ILLEGAL_TYPE ty
                     on ty.code = p.illegaltype_id
                   left join EA_ILLEGAL_EVIDENCE e
                     on e.illegal_id = p.illegaltype_id
                   left join EA_PUBLIC_RESOURCE re
                     on re.id = e.resource_id
                   left join EA_PUBLIC_DEVICE d
                     on d.id = p.device_id
                   left join EA_PUBLIC_ROAD r
                     on r.id = d.road_id
                   left join EA_PUBLIC_ROAD_DIRECTION rd
                     on rd.road_id = d.road_id
                  where e.type = ""F"") UNION
                (select rej.id,
                        rej.plate,
                        rej.device_id,
                        rej.platecolor_code,
                        rej.platetype_code,
                        rej.cartype_code,
                        rej.carcolor_code,
                        rej.catchspeed,
                        rej.catchtime,
                        ty2.code,
                        ty2.name,
                        e2.resource_id,
                        e2.type,
                        re2.local,
                        re2.remote,
                        re2.absolute,
                        r2.name,
                        rd2.name,
                        rd2.lane_number
                   from EA_ILLEGAL_REJECT rej
                   left join EA_ILLEGAL_TYPE ty2
                     on ty2.code = rej.illegaltype_id
                   left join EA_ILLEGAL_EVIDENCE e2
                     on e2.illegal_id = rej.illegaltype_id
                   left join EA_PUBLIC_RESOURCE re2
                     on re2.id = e2.resource_id
                   left join EA_PUBLIC_DEVICE d2
                     on d2.id = rej.device_id
                   left join EA_PUBLIC_ROAD r2
                     on r2.id = d2.road_id
                   left join EA_PUBLIC_ROAD_DIRECTION rd2
                     on rd2.road_id = d2.road_id
                  where e2.type = ""F"")) page)
 WHERE rn BETWEEN 1 AND 20

报错为:
Cause: java.sql.SQLSyntaxErrorException: ORA-00918: 未明确定义列
我就纳闷了,究竟哪里有问题了,单独执行一下的SQL是正常的

(select p.id,
                        p.plate,
                        p.device_id,
                        p.platecolor_code,
                        p.platetype_code,
                        p.cartype_code,
                        p.carcolor_code,
                        p.catchspeed,
                        p.catchtime,
                        ty.code,
                        ty.name,
                        e.resource_id,
                        e.type,
                        re.local,
                        re.remote,
                        re.absolute,
                        r.name,
                        rd.name,
                        rd.lane_number
                   from EA_ILLEGAL_PASS p
                   left join EA_ILLEGAL_TYPE ty
                     on ty.code = p.illegaltype_id
                   left join EA_ILLEGAL_EVIDENCE e
                     on e.illegal_id = p.illegaltype_id
                   left join EA_PUBLIC_RESOURCE re
                     on re.id = e.resource_id
                   left join EA_PUBLIC_DEVICE d
                     on d.id = p.device_id
                   left join EA_PUBLIC_ROAD r
                     on r.id = d.road_id
                   left join EA_PUBLIC_ROAD_DIRECTION rd
                     on rd.road_id = d.road_id
                  where e.type = ""F"") UNION
                (select rej.id,
                        rej.plate,
                        rej.device_id,
                        rej.platecolor_code,
                        rej.platetype_code,
                        rej.cartype_code,
                        rej.carcolor_code,
                        rej.catchspeed,
                        rej.catchtime,
                        ty2.code,
                        ty2.name,
                        e2.resource_id,
                        e2.type,
                        re2.local,
                        re2.remote,
                        re2.absolute,
                        r2.name,
                        rd2.name,
                        rd2.lane_number
                   from EA_ILLEGAL_REJECT rej
                   left join EA_ILLEGAL_TYPE ty2
                     on ty2.code = rej.illegaltype_id
                   left join EA_ILLEGAL_EVIDENCE e2
                     on e2.illegal_id = rej.illegaltype_id
                   left join EA_PUBLIC_RESOURCE re2
                     on re2.id = e2.resource_id
                   left join EA_PUBLIC_DEVICE d2
                     on d2.id = rej.device_id
                   left join EA_PUBLIC_ROAD r2
                     on r2.id = d2.road_id
                   left join EA_PUBLIC_ROAD_DIRECTION rd2
                     on rd2.road_id = d2.road_id
                  where e2.type = ""F"")

麻烦各位帮我瞧瞧,剩下最后20分了

自己解决了
是几个name 命名重复了

10分
Union里的列名与主select里的列名重复了也不行,是吧?

10分
看了下楼主的SQL,你单独能运行,说明问题出现在分页那块,不知道你数据库是什么,另外你是在同一张表里面进行查询,只条件不同,不用那么复杂的,用IN替代赛!
1.你尝试把别名的AS去掉试试
2.可能是你查询结果列名重复,试试给字段都加上别名。
这只是我看代码臆测的,希望能帮到楼主!
怎么将mysql语句改成ibatis啊?
具体的语句为:select * from tb_d_project where now()<Pr_EndTime and now()>=Pr_BeginTime and PS_Id=””3″”
union 
select * from tb_d_project where ISNULL(Pr_EndTime) and (now()>=Pr_BeginTime OR ISNULL(Pr_BeginTime)) and PS_Id=””3″”
union
select * from tb_d_project where ISNULL(Pr_BeginTime) and (NOW()<=Pr_EndTime OR ISNULL(Pr_EndTime)) and PS_Id=””3″”;

CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明ibatis执行sql报:未明确定义列