刚接触IBATIS,希望大家能够帮忙看一下.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">
select ltrim(substr(report_date,7,2),0) day,
min(starttime) starttime,
max(endtime) endtime,
case
when min(starttime)[color=red][b]<=[/b][/color]'12:00' and max(endtime)<='12:00'
then (to_date(max(endtime),'hh24:mi')-to_date(min(starttime),'hh24:mi'))*24
when (min(starttime)>='12:00' and min(starttime)<='13:00') and (max(endtime)>='12:00' and max(endtime)<='13:00')
then 0
when min(starttime)>='13:00' and max(endtime)>='13:00'
then (to_date(max(endtime),'hh24:mi')-to_date(min(starttime),'hh24:mi'))*24
when min(starttime)<='12:00' and (max(endtime)>='12:00' and max(endtime)<='13:00')
then (to_date('12:00','hh24:mi')-to_date(min(starttime),'hh24:mi'))*24
when min(starttime)<='12:00' and max(endtime)>='13:00'
then (to_date(max(endtime),'hh24:mi')-to_date(min(starttime),'hh24:mi'))*24-1
when (min(starttime)>='12:00' and min(starttime)<='13:00') and max(endtime)>='13:00'
then (to_date(max(endtime),'hh24:mi')-to_date('13:00','hh24:mi'))*24
end worktime,
report_date,substr(max(sys_connect_by_path(workcontent,',')),2) workcontent,
max(department) department,
max(team) team,
max(confirm) confirm
from (select dailyreport.*,row_number() over(partition by report_date order by workcontent) rn from dailyreport)
where login_id=#loginid# and substr(report_date,0,6)=#reportdate#
group by report_date
start with rn=1
connect by rn-1=prior rn and report_date=prior report_date
问题:
如粗体红色部分所示,IBATIS中这样写报错.在网上查了一下,除等于外一般用动态SQL这种写法
问题是我的写在CASE WHEN 语句中,应该怎么样修改....迷茫中...等待大好人....
