Question

sql语句在数据库里可以查到，但是通过代码执行就报未明确的定义列是为什么？

PreparedStatementCallback; bad SQL grammar [select * from (select row_.*,rownum rownum_ from (select a.year as year,a.mount as month,a.departname as orgName,a.chName as chName,a.aiName as aiName,a.departname as orgName,a.chName as chName,a.aiName as aiName,a.xbAmount as xbAmount,b2.amount as adjAmount from (select distinct x.year year,x.month mount,tsd.id id,tsd.departname departname,x.channel channel,x.channel_name chName,xai.ai_code code,xai.ai_name aiName,x.amount xbAmount from xps_budget x left join xps_account_item xai on x.xps_account_item_id=xai.id left join t_s_depart tsd on x.org_id=tsd.id where to_char(to_date(x.month,'mm'),'mm')=to_char(sysdate,'mm')) a left join (select a1.src_org_id,a1.channel,a1.ai_code,sum(a1.amount) amount from (select b.src_org_id src_org_id,xb.channel channel,xa.ai_code ai_code,b.amount amount from xps_budget xb join bpm_xps_bgt_adjust_log b on xb.id=b.src_budget_id join xps_account_item xa on b.src_account_item_id=xa.id where to_char(to_date(xb.month,'mm'), 'mm')=to_char(sysdate,'mm') union select b1.dst_org_id,xb.channel,x1.ai_code,b1.amount adjAmount from xps_budget xb join bpm_xps_bgt_adjust_log b1 on xb.id=b1.dst_budget_id join xps_account_item x1 on b1.dst_account_item_id=x1.id where to_char(to_date(xb.month,'mm'),'mm')=to_char(sysdate,'mm')) a1 group by a1.src_org_id,a1.channel,a1.ai_code) b2 on a.id=b2.src_org_id and a.channel=b2.channel and a.code=b2.ai_code) row_ where rownum <= 10000) where rownum_>0]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00918: 未明确定义列

Answer 1

多个表 或者构造 查询都包含有 相同的字段，你就不知道是查哪个表中的数据了。

比如 A表中 含有 C1,C2,C3 三个字段，B表中含有 C1,C2字段
SQL语句 :SELECT C1,C2 ,C3 FROM A, B
WHERE A.C1=B.C1
的时候 数据库不知道 是从A取C1 还是从B 取 C1
要加表名 前缀就好了
如：
SELECT A.C1,A.C2 ,A.C3 FROM A, B
WHERE A.C1=B.C1

Answer 2

b.amount amount
from xps_budget xb
join bpm_xps_bgt_adjust_log b on xb.id =
b.src_budget_id
join xps_account_item xa on b.src_account_item_id =
xa.id
where to_char(to_date(xb.month, 'mm'),
'mm') =
to_char(sysdate, 'mm')
union
select b1.dst_org_id,
xb.channel,
x1.ai_code,
b1.amount adjAmount
from xps_budget xb
join bpm_xps_bgt_adjust_log b1 on xb.id =

有2个xb 表别名，所以报错~

Answer 3

由于这是多张表合在一起的，建议不要用select * 的写法，应该把需要的字段手动提取出来

追问

select * 是后台自己生成的