我的原始SQL:
SELECT ${if(and(类型='门诊',统计类型='ORDER_DEPT'),"ORDER_DEPT_NAME",统计类型)} "科室",item_class "类别","SUM"(TOTAL_COSTS) "收入"
from ${if(类型='门诊',"CLINIC_BILL_DETAIL d join CLINIC_BILL_MASTER m
on (d.RECEIPT_NO=m.RECEIPT_NO and d.CASHIER_ID=m.CASHIER_ID)","INP_SETTLE s join INP_BILL_DETAIL b
on (s.PATIENT_ID=b.PATIENT_ID and s.VISIT_ID=b.VISIT_ID)")}
where 1=1
${if(and(类型='门诊',type='date'),"and m.checkout_date= to_date('"+day+"','yyyy-MM-dd')","")}
${if(and(类型='住院',type='date'),"and ACCOUNT_DATE= to_date('"+day+"','yyyy-MM-dd')","")}
${if(and(类型='门诊',type='month'),"and m.checkout_date between trunc(to_date('"+month+"','yyyy-MM'),'mm') and
add_months(trunc(to_date('"+month+"','yyyy-MM'),'mm'),1)-1 ","")}
${if(and(类型='住院',type='month'),"and ACCOUNT_DATE between trunc(to_date('"+month+"','yyyy-MM'),'mm') and
add_months(trunc(to_date('"+month+"','yyyy-MM'),'mm'),1)-1 ","")}
${if(and(类型='门诊',type='year'),"and m.checkout_date BETWEEN trunc(to_date('"+year+"','yyyy'),'yy') AND add_months(trunc(to_date('"+year+"','yyyy'),'yy'),12)-1","")}
${if(and(类型='住院',type='year'),"and ACCOUNT_DATE BETWEEN trunc(to_date('"+year+"','yyyy'),'yy') AND add_months(trunc(to_date('"+year+"','yyyy'),'yy'),12)-1","")}
GROUP BY ${if(and(类型='门诊',统计类型='ORDER_DEPT'),"ORDER_DEPT_NAME",统计类型)},item_class
order by "收入"
修改后的SQL:
SELECT ${if(and(类型='门诊',统计类型='ORDER_DEPT'),"ORDER_DEPT_NAME",统计类型)} "科室",item_class "类别","SUM"(TOTAL_COSTS) "收入"
from ${if(类型='门诊',"CLINIC_BILL_DETAIL d join CLINIC_BILL_MASTER m
on (d.RECEIPT_NO=m.RECEIPT_NO and d.CASHIER_ID=m.CASHIER_ID)","INP_SETTLE s join INP_BILL_DETAIL b
on (s.PATIENT_ID=b.PATIENT_ID and s.VISIT_ID=b.VISIT_ID)")}
where 1=1
${if(and(类型='门诊'),"and m.checkout_date BETWEEN to_date('${starttime}','YYYY-MM-DD') and to_date('${endtime}','YYYY-MM-DD')")}
${if(and(类型='住院'),"and ACCOUNT_DATE BETWEEN to_date('${starttime}','YYYY-MM-DD') and to_date('${endtime}','YYYY-MM-DD')")}
GROUP BY ${if(and(类型='门诊',统计类型='ORDER_DEPT'),"ORDER_DEPT_NAME",统计类型)},item_class
order by "收入"
然后报错:
86988
求大神解答。。。。。。。