Btw, I think the problem is in the #date:VARCHAR# part. Why don't you put the date in as :DATE or :TIMESTAMP ? Did you try to put com.ibatis and java.sql log to debug, so you see the exact parameter going into the #date# parameter?
DelGurth On Fri, Jun 12, 2009 at 4:44 PM, DelGurth<[email protected]> wrote: > To me that COUNT seems like an alias for the result of COUNT(CD) as in > SELECT COUNT(CD) AS COUNT > > But, what is the error? > > DelGurth > > On Fri, Jun 12, 2009 at 3:35 PM, Brandon Goodin<[email protected]> > wrote: >> I didn't look over it thoroughly but i noticed you appear to have a >> misplaced "COUNT": >> SELECT CG , COUNT(CD) COUNT, >> SUM ( >> TO_DATE(EDATE||' '|| etime ,'DD-MM-YYYY HH24:MI:SS' ) - >> TO_DATE(ADATE||' '|| atime ,'DD-MM-YYYY HH24:MI:SS' ) >> ) total_time >> Perhaps you should check you SQL. >> Brandon >> >> >> On Fri, Jun 12, 2009 at 12:44 AM, Jahan Zaib <[email protected]> >> wrote: >>> >>> i am trying to run this but problem occurs >>> i have a class >>> ---- >>> public class CompleteCallsReportBean implements Serializable{ >>> /** >>> * >>> */ >>> private static final long serialVersionUID = 2192836100387342196L; >>> /** >>> * >>> */ >>> private String cg; >>> private String COUNT; >>> private String totalTime; >>> public void setCG(String cg) { >>> this.cg = cg == null ? null : cg.trim(); >>> } >>> public void setCOUNT(String COUNT) { >>> this.COUNT = COUNT == null ? null : COUNT.trim(); >>> } >>> public void setTotalTime(String totalTime) { >>> this.totalTime = totalTime == null ? null : totalTime.trim(); >>> } >>> public String getCG() { >>> return cg; >>> } >>> public String getCOUNT() { >>> return COUNT ; >>> } >>> public String getTotalTime() { >>> return totalTime; >>> } >>> } >>> >>> -------- >>> And Sql Map related to that is >>> : >>> <resultMap id="CompleteCallsReportMap" >>> class="com.prevail.bean.CompleteCallsReportBean" > >>> <result column="total_time" property="totalTime" jdbcType="VARCHAR" /> >>> <result column="COUNT" property="COUNT" jdbcType="VARCHAR" /> >>> <result column="CG" property="cg" jdbcType="VARCHAR" /> >>> </resultMap> >>> >>> <select id="get_complete_call_summary_by_date" parameterClass="String" >>> resultMap="CompleteCallsReportMap" > >>> SELECT CG , COUNT(CD) COUNT, >>> SUM ( >>> TO_DATE(EDATE||' '|| etime ,'DD-MM-YYYY HH24:MI:SS' ) - >>> TO_DATE(ADATE||' '|| atime ,'DD-MM-YYYY HH24:MI:SS' ) >>> ) total_time >>> FROM COMPLETE_CALLS >>> WHERE EDATE = #date:VARCHAR# HAVING >>> ( >>> TRUNC (SUM ( >>> TO_DATE(EDATE||' '|| etime >>> ,'DD-MM-YYYY HH24:MI:SS' ) - TO_DATE(ADATE||' '|| atime ,'DD-MM-YYYY >>> HH24:MI:SS' ) >>> ) *24 >>> ) >>> BETWEEN 0 AND 9 >>> ) >>> GROUP BY >>> CG order by total_time desc >>> </select> >>> --------------------------- >>> But it did not fetch any data, where as simply query run fine >>> What i do ? >>> Kindly Help >>> -- >>> Regards, >>> Jahan Zaib >>> >> >> >
