Question

如下SQL一直报错 ：ORA-00923：无法在应该出现from关键字的位置上找到它。可在PL/SQL中执行没问题，求指导

"select id_no_sz 工号,name_sz 姓名,max(dir) 直间接,max(decode(ban,'D01+E01',days,0)) 天数1,max(decode(ban,'D02+E02',days,0)) 天数2,sum(acount) 补贴金额"+
"from (select h.id_no_sz,"+
"h.name_sz,"+
"decode(c.shift_group_id,'2128','D01+E01','2148','D01+E01','2129','D02+E02','2149','D02+E02') ban,"+
"decode(h.jobcategory, 'JO01', '直接', 'JO02', '间接') dir,"+
"count(c.cday) days,"+
"(case"+
"when (c.shift_group_id in ('2128', '2148')) then"+
"count(c.cday) * 8"+
" else"+
" count(c.cday) * 12"+
" end) acount"+
"from hr_carding c, hr_personnel_base h"+
"where h.id = c.psn_id"+
"and c.psn_seg_segment_no = '4274'"+
"and c.shift_group_id in ('2148', '2128', '2149', '2129')"+
"and c.cday >= to_date('" + t1 + "', 'yyyy-mm-dd hh24:mi:ss')" +
"and c.cday <= to_date('" + t2 + "', 'yyyy-mm-dd hh24:mi:ss')" +
"and (c.outactual - c.inactual) * 24 * 60 > 360"+
"group by h.id_no_sz, h.name_sz, c.shift_group_id, h.jobcategory)"+
"group by id_no_sz,name_sz";

Answer 1

"select
id_no_sz 工号,
name_sz 姓名,
max(dir) 直间接,
max(decode(ban,'D01+E01',days,0)) 天数1,
max(decode(ban,'D02+E02',days,0)) 天数2,

这里 需要加一个 空格, 把 补贴金额 和 from 分割开
sum(acount) 补贴金额"+
"from (select h.id_no_sz,"+

"h.name_sz,"+
"decode(c.shift_group_id,'2128','D01+E01','2148','D01+E01','2129','D02+E02','2149','D02+E02') ban,"+
"decode(h.jobcategory, 'JO01', '直接', 'JO02', '间接') dir,"+
"count(c.cday) days,"+
"(case"+
"when (c.shift_group_id in ('2128', '2148')) then"+
"count(c.cday) * 8"+
" else"+
" count(c.cday) * 12"+

这里 需要加一个 空格, 把 acount 和 from 分割开
" end) acount"+
"from hr_carding c, hr_personnel_base h"+

"where h.id = c.psn_id"+
"and c.psn_seg_segment_no = '4274'"+
"and c.shift_group_id in ('2148', '2128', '2149', '2129')"+
"and c.cday >= to_date('" + t1 + "', 'yyyy-mm-dd hh24:mi:ss')" +
"and c.cday <= to_date('" + t2 + "', 'yyyy-mm-dd hh24:mi:ss')" +
"and (c.outactual - c.inactual) * 24 * 60 > 360"+

没什么以外的话， 这里也要加 空格 把 360 和 group by 分开
"group by h.id_no_sz, h.name_sz, c.shift_group_id, h.jobcategory)"+
"group by id_no_sz,name_sz";

Answer 2

你如果在pl/sql中执行没有问题就打印一下编译后的全部sql文，在那里看看那块出问题了，用System.printout就可以打印~