在ibatis中使用oracle的sql语句,但是无法解析 case when ....end 语句
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"><sqlMap namespace="mapDataDay">
<select id="showmapout" resultClass="java.util.HashMap">
<![CDATA[
select t.acct_month "acct_month",
t.day_id "day_id",
t.area_no "area_no",
t3.mnemonic "mnemonic",
t2.area_name "area_name",
TO_CHAR(sum(t.pay_fee) / 10000,'FM999,999,999,990.0') "pay_fee",
TO_CHAR(sum(t.pay_fee_hb) / 10000,'FM999,999,999,990.0') "pay_fee_hb",
TO_CHAR(sum(t.pay_fee - t.pay_fee_hb) / 10000,'FM999,999,999,990.0') "zz",
case when sum(t.pay_fee_hb)=0 then to_char(100) else
TO_CHAR((sum(t.pay_fee - t.pay_fee_hb) / sum(t.pay_fee_hb)) * 100,
'FM999,999,999,990.0') end "zz_bfb",
'万元' "kpi_dw"
from dm.dm_sett_call_portal_d t,
dim.dim_area_code t2,
dim.dim_area_mnemonic t3
where t.acct_month = 201102
and t.day_id = 01 and t.area_no = t2.area_code
and t2.area_code = t3.area_no
and t.pay_type in ('02', '05')
and t.agent_no not like '0202%'
group by t.area_no, t.acct_month, t2.area_name, t3.mnemonic,t.day_id,t.pay_fee_hb
order by sum(t.pay_fee - t.pay_fee_hb) ]]>
</select>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"><sqlMap namespace="mapDataDay">
<select id="showmapout" resultClass="java.util.HashMap">
<![CDATA[
select t.acct_month "acct_month",
t.day_id "day_id",
t.area_no "area_no",
t3.mnemonic "mnemonic",
t2.area_name "area_name",
TO_CHAR(sum(t.pay_fee) / 10000,'FM999,999,999,990.0') "pay_fee",
TO_CHAR(sum(t.pay_fee_hb) / 10000,'FM999,999,999,990.0') "pay_fee_hb",
TO_CHAR(sum(t.pay_fee - t.pay_fee_hb) / 10000,'FM999,999,999,990.0') "zz",
case when sum(t.pay_fee_hb)=0 then to_char(100) else
TO_CHAR((sum(t.pay_fee - t.pay_fee_hb) / sum(t.pay_fee_hb)) * 100,
'FM999,999,999,990.0') end "zz_bfb",
'万元' "kpi_dw"
from dm.dm_sett_call_portal_d t,
dim.dim_area_code t2,
dim.dim_area_mnemonic t3
where t.acct_month = 201102
and t.day_id = 01 and t.area_no = t2.area_code
and t2.area_code = t3.area_no
and t.pay_type in ('02', '05')
and t.agent_no not like '0202%'
group by t.area_no, t.acct_month, t2.area_name, t3.mnemonic,t.day_id,t.pay_fee_hb
order by sum(t.pay_fee - t.pay_fee_hb) ]]>
</select>
解决方案 »
- dbms_rowid.create_rowid 中的object_number 问题
- windows 7 下安装PL/SQL DEVELOPER 7.1.5.1398 问题
- VC7+OCCI连接ORACLE 成功运行之后文件一直被占用,无法Update
- 求:oracle md5 函数及用法
- 这个程序包到底哪错了,一直报错
- 在线高分求救:索引AEWS.PK_TS_ITEMVALUE无法通过5394(在表空间AEWS中)扩展
- 执行存储过程出错
- 如何停止oracle的web服务器 servlet
- 新手求助
- 请问:我想只取第100到300条的纪录,应该怎么做呢?
- 如何实现这样的查询呢
- 一个字段多条记录,如果进行and 查询
--用decode()函数试试
decode(sum(t.pay_fee_hb),
0,to_char(100),
to_char((sum(t.pay_fee - t.pay_fee_hb)/sum(t.pay_fee_hb))*100,'FM999,999,999,990.0'))
select sba.BS_AGREEMENT_0025,sba.BS_AGREEMENT_0001,sba.BS_AGREEMENT_0007,
sum(case when sbf.BS_FEESTANDORD_ID =5 then sbf.BS_FEE_0005 else 0 end),
max(case when sbf.BS_FEESTANDORD_ID =5 then sbf.BS_FEE_0001 end),
sum(case when sbf.BS_FEESTANDORD_ID =2 then sbf.BS_FEE_0004 else 0 end),
sum(case when sbf.BS_FEESTANDORD_ID =2 then sbf.BS_FEE_0005 else 0 end),
max(case when sbf.BS_FEESTANDORD_ID =2 then sbf.BS_FEE_0001 end),
sum(case when sbf.BS_FEESTANDORD_ID =2 then sbf.BS_FEE_0006 else 0 end),
sum(case when sbf.BS_FEESTANDORD_ID =3 then sbf.BS_FEE_0004 else 0 end),
sum(case when sbf.BS_FEESTANDORD_ID =3 then sbf.BS_FEE_0005 else 0 end),
sum(case when sbf.BS_FEESTANDORD_ID =3 then sbf.BS_FEE_0006 else 0 end),
max(case when sbf.BS_FEESTANDORD_ID =3 then sbf.BS_FEE_0001 end)
from SCCBB_BS_FEE sbf,SCCBB_BS_AGREEMENT sba
where to_char(sba.BS_AGREEMENT_0007,'yyyy-MM')=to_char(#date#,'yyyy-MM')
and sbf.BS_AGREEMENT_ID = sba.BS_AGREEMENT_ID
and to_char(sbf.BS_FEE_0001,'yyyy-MM')=to_char(#date#,'yyyy-MM')
group by sba.BS_AGREEMENT_0025, sba.BS_AGREEMENT_0001, sba.BS_AGREEMENT_0007
我把ibatis中生成的sql在sqldeveloper中运行,没有错误。但是在ibatis中运行报Cause: com.ibatis.common.beans.ProbeException: There is no WRITEABLE property named 'BS_FEESTANDORD_ID=5THENSBF' in class 'java.lang.Object'错误,这种错误类型是在做回调的时候JAVABEAN报错,也就是sum(case when sbf.BS_FEESTANDORD_ID =5 then sbf.BS_FEE_0005 else 0 end)等不能作为一个bean的property,所以会报错。
解决方法是加别名,英文的别名。
select sba.BS_AGREEMENT_0025,sba.BS_AGREEMENT_0001,sba.BS_AGREEMENT_0007,
sum(case when sbf.BS_FEESTANDORD_ID =5 then sbf.BS_FEE_0005 else 0 end) s1,
max(case when sbf.BS_FEESTANDORD_ID =5 then sbf.BS_FEE_0001 end) d1,
sum(case when sbf.BS_FEESTANDORD_ID =2 then sbf.BS_FEE_0004 else 0 end) s21,
sum(case when sbf.BS_FEESTANDORD_ID =2 then sbf.BS_FEE_0005 else 0 end) s22,
max(case when sbf.BS_FEESTANDORD_ID =2 then sbf.BS_FEE_0001 end) d2,
sum(case when sbf.BS_FEESTANDORD_ID =2 then sbf.BS_FEE_0006 else 0 end) s23,
sum(case when sbf.BS_FEESTANDORD_ID =3 then sbf.BS_FEE_0004 else 0 end) s31,
sum(case when sbf.BS_FEESTANDORD_ID =3 then sbf.BS_FEE_0005 else 0 end) s32,
sum(case when sbf.BS_FEESTANDORD_ID =3 then sbf.BS_FEE_0006 else 0 end) s33,
max(case when sbf.BS_FEESTANDORD_ID =3 then sbf.BS_FEE_0001 end) d3
from SCCBB_BS_FEE sbf,SCCBB_BS_AGREEMENT sba
where to_char(sba.BS_AGREEMENT_0007,'yyyy-MM')=to_char(#date#,'yyyy-MM')
and sbf.BS_AGREEMENT_ID = sba.BS_AGREEMENT_ID
and to_char(sbf.BS_FEE_0001,'yyyy-MM')=to_char(#date#,'yyyy-MM')
group by sba.BS_AGREEMENT_0025, sba.BS_AGREEMENT_0001, sba.BS_AGREEMENT_0007
这样就OK了。
当resultClass是hashMap的时候往往忘记了JavaBean这个东西。