String sql= "select code,name,nvl(h,0) h,nvl(z,0) z,nvl(c,0) c,nvl(abs(cont-(h+z+c)/items),0) wtp,"
+ "nvl(case when cont*items=0 then 0 else round((h*100)/(cont*items),2) end,0) ypl from "
+ "(with c as (select count(*) items from t_asse_pro_struct s where s.pro_id = (select pro_id from t_asse_oject_troupe_middle t "
+ "where t.asse_status='1' and t.asse_type='1' group by pro_id)) "
+ "select b.code,b.name,nvl(sum(a.inner_h),0) h,nvl(sum(a.inner_z),0) z,nvl(sum(a.inner_c),0) c,c.items,"
+ "(select count(*) from a01 where a0132 like f_del_char(b.code)||'#' and a0199<'5') cont "
+ "from (select max(a1.troupe_code) troupe_code,sum(a1.inner_h+b1.asse_scale) inner_h,sum(a1.inner_z) inner_z,sum(a1.inner_c) inner_c from"
+"t_asse_troupe_statis a1,(select t.*,s.id s_id,s.pro_type,s.status from t_asse_troupe_list t,t_asse_usual_times s where t.pro_id=s.pro_id and"
+"substr(to_char(t.asse_date,'yyyy-MM-dd'),1,7)=s.asse_date and s.asse_date='2013-08')b1 where a1.pro_id=b1.pro_id) a,"
+ "(select t.* from t_asse_troupe t,t_asse_oject_troupe_middle o where t.code=o.dw_code "
+ "and o.asse_status='1' and o.asse_type='1' and instr(o.asse_range,'4')>0 %s) b,c "
+ "where a.troupe_code(+)=b.code group by b.code,b.name,c.items) "
+ "order by ypl desc,z desc,c desc,wtp desc";if (troupe_type != null && !"".equals(troupe_type)) {
if ("under".equals(troupe_type)) {
sql = String.format(sql, "and t.type='1'");
} else if ("subarea".equals(troupe_type)) {
sql = String.format(sql, "and t.type in ('2','3')");
} else if ("county".equals(troupe_type)) {
sql = String.format(sql, "and t.type='4'");
}
} else {
sql = String.format(sql, "and 1=1");
}
运行程序就出现了 ORA-00923: 未找到预期 FROM 关键字
但是将sql语句拿到数据库去执行,又能成功
+ "nvl(case when cont*items=0 then 0 else round((h*100)/(cont*items),2) end,0) ypl from "
+ "(with c as (select count(*) items from t_asse_pro_struct s where s.pro_id = (select pro_id from t_asse_oject_troupe_middle t "
+ "where t.asse_status='1' and t.asse_type='1' group by pro_id)) "
+ "select b.code,b.name,nvl(sum(a.inner_h),0) h,nvl(sum(a.inner_z),0) z,nvl(sum(a.inner_c),0) c,c.items,"
+ "(select count(*) from a01 where a0132 like f_del_char(b.code)||'#' and a0199<'5') cont "
+ "from (select max(a1.troupe_code) troupe_code,sum(a1.inner_h+b1.asse_scale) inner_h,sum(a1.inner_z) inner_z,sum(a1.inner_c) inner_c from"
+"t_asse_troupe_statis a1,(select t.*,s.id s_id,s.pro_type,s.status from t_asse_troupe_list t,t_asse_usual_times s where t.pro_id=s.pro_id and"
+"substr(to_char(t.asse_date,'yyyy-MM-dd'),1,7)=s.asse_date and s.asse_date='2013-08')b1 where a1.pro_id=b1.pro_id) a,"
+ "(select t.* from t_asse_troupe t,t_asse_oject_troupe_middle o where t.code=o.dw_code "
+ "and o.asse_status='1' and o.asse_type='1' and instr(o.asse_range,'4')>0 %s) b,c "
+ "where a.troupe_code(+)=b.code group by b.code,b.name,c.items) "
+ "order by ypl desc,z desc,c desc,wtp desc";if (troupe_type != null && !"".equals(troupe_type)) {
if ("under".equals(troupe_type)) {
sql = String.format(sql, "and t.type='1'");
} else if ("subarea".equals(troupe_type)) {
sql = String.format(sql, "and t.type in ('2','3')");
} else if ("county".equals(troupe_type)) {
sql = String.format(sql, "and t.type='4'");
}
} else {
sql = String.format(sql, "and 1=1");
}
运行程序就出现了 ORA-00923: 未找到预期 FROM 关键字
但是将sql语句拿到数据库去执行,又能成功
扯淡,数据库累死了。我认为更好的选择是用一些单纯的SQL(只用必要的几个联合查询),来处理数据,
在应用服务器能承受的范围内,来进行数据的转换整理。不要把负担都加给数据库,让应用服务器也分担一些。