Question

在程序中不能执行但是在plsql就能执行，急 求高手解答！

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select * from (select rownum as my_rownum,table_a.* from( select psg.issue_time as issueTicketDate, psg.tkt_status as pzzt, fgt.air_comp as airComp, psg.tkt_no as tktNo, psg.psg_name as passenger, psg.tkt_price as ticketPrice, psg.tkt_constax as consTax, psg.tkt_fueltax as fuelTax, psg.revable_otherconsts as qt1, psg.refund_price as qt2, psg.insu_price as insuAmount, tor.order_discount as discountAmount, tor.book_empno as csNo, TO_DATE(TO_CHAR(tor.book_time, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') as bookingDate, tor.confirm_name as bookingUser, psg.psg_deptname as passengerDeptName, psg.psg_duty as passengerDuty, psg.psg_evectionno as evectionNo, tor.order_remark as remark, TO_DATE(TO_CHAR(fgt.fly_time, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') as flyDate, wm_concat(fgt.org_city || '-' || fgt.arr_city) as flightLines, wm_concat(fgt.flight_no) as flightNo, wm_concat(fgt.class_code) as cabin, wm_concat(bx.policyno) as insuNo from t_dometicketpsg psg left join t_dometicketorder tor on psg.order_id = tor.order_id left join t_dometicketflight fgt on fgt.flight_id = psg.flight_id left join KH_BDXX bx on tor.order_no = bx.ddbh left join t_dometicketdeli kel on tor.order_id = kel.order_id where 3 = 3 and TO_CHAR(fgt.fly_time,'YYYY-MM-DD HH24:MI:SS') >= ? and TO_CHAR(fgt.fly_time,'YYYY-MM-DD HH24:MI:SS') <= ? group by psg.tkt_no, psg.issue_time, psg.tkt_status, fgt.air_comp, psg.psg_name, psg.tkt_price, psg.tkt_constax, psg.tkt_fueltax, psg.revable_otherconsts, psg.refund_price, psg.insu_price, tor.order_discount, tor.book_empno, tor.book_time, tor.confirm_name, psg.psg_deptname, psg.psg_duty, tor.order_remark, TO_DATE(TO_CHAR(tor.book_time, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'), TO_DATE(TO_CHAR(fgt.fly_time, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'), psg.psg_evectionno order by tor.book_time) table_a where rownum=?]; SQL state [null]; error code [17006]; ????; nested exception is java.sql.SQLException: ????
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate

若以下回答无法解决问题，邀请你更新回答

Answer 1

ora-17006 列名无效
重新检查下sql
另外上面打印出来的sql好像少了一个括号。

追问

sql摘出来在PLSQL中执行是没问题的，但是后台就是报列明无效。sql保证是没问题的！

追答

select * from (select rownum as my_rownum,table_a.* from( select psg.issue_time as issueTicketDate, psg.tkt_status as pzzt, fgt.air_comp as airComp, psg.tkt_no as tktNo, psg.psg_name as passenger, psg.tkt_price as ticketPrice, psg.tkt_constax as consTax, psg.tkt_fueltax as fuelTax, psg.revable_otherconsts as qt1, psg.refund_price as qt2, psg.insu_price as insuAmount, tor.order_discount as discountAmount, tor.book_empno as csNo, TO_DATE(TO_CHAR(tor.book_time, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') as bookingDate, tor.confirm_name as bookingUser, psg.psg_deptname as passengerDeptName, psg.psg_duty as passengerDuty, psg.psg_evectionno as evectionNo, tor.order_remark as remark, TO_DATE(TO_CHAR(fgt.fly_time, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') as flyDate, wm_concat(fgt.org_city || '-' || fgt.arr_city) as flightLines, wm_concat(fgt.flight_no) as flightNo, wm_concat(fgt.class_code) as cabin, wm_concat(bx.policyno) as insuNo from t_dometicketpsg psg left join t_dometicketorder tor on psg.order_id = tor.order_id left join t_dometicketflight fgt on fgt.flight_id = psg.flight_id left join KH_BDXX bx on tor.order_no = bx.ddbh left join t_dometicketdeli kel on tor.order_id = kel.order_id where 3 = 3 and TO_CHAR(fgt.fly_time,'YYYY-MM-DD HH24:MI:SS') >= ? and TO_CHAR(fgt.fly_time,'YYYY-MM-DD HH24:MI:SS') <= ? group by psg.tkt_no, psg.issue_time, psg.tkt_status, fgt.air_comp, psg.psg_name, psg.tkt_price, psg.tkt_constax, psg.tkt_fueltax, psg.revable_otherconsts, psg.refund_price, psg.insu_price, tor.order_discount, tor.book_empno, tor.book_time, tor.confirm_name, psg.psg_deptname, psg.psg_duty, tor.order_remark, TO_DATE(TO_CHAR(tor.book_time, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'), TO_DATE(TO_CHAR(fgt.fly_time, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS'), psg.psg_evectionno order by tor.book_time) table_a where rownum=?

这是上面报错中的sql。