为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:dm8
【操作系统】win10
【CPU】:i5-11400h
【问题描述】*:这是在IDEA操作数据库和在dm查询时一直提示字符串转换出错,
to_date函数在dm也能正常查询出数据,暂时我没找出错在哪里,然后数据库的CALL_START_TIME是TIMESTAMP数据类型,其他的都是int类型,
SELECT
TO_DATE(CALL_START_TIME, 'yyyy-mm-dd') AS statDate,
COUNT() AS totalCalls,
SUM(CASE WHEN call_key ='IVR' THEN 1 ELSE 0 END) AS ivrCount,
SUM(CASE WHEN call_result = 1 THEN 1 ELSE 0 END) AS connectedCount,
ROUND(
(SUM(CASE WHEN call_result = 1 THEN 1 ELSE 0 END) / COUNT()) * 100.0,
2
)|| '%' AS connectionRate
FROM v_call_records
<where>
<if test="startTime != null and startTime != ''">
AND call_start_time >= #{startTime}
</if>
<if test="endTime != null and endTime != ''">
AND call_end_time <= #{endTime}
</if>
<if test="userId != null and userId != ''">
AND user_id = #{userId}
</if>
</where>
GROUP BY TO_DATE(CALL_START_TIME, 'yyyy-mm-dd')
ORDER BY statdate ASC;
这个涉及到的建表SQL发出来一份,把他转换成SQL放到管理工具下再看
CALL_START_TIME
原本就是时间类型,加TO_DATE()应该会隐式转换。把这个TO_DATE()去掉试试呢
去除计算也还是报错