SELECT tmp.开始, tmp.结束, tmp.减小时与不减, tmp.OT_MINUTE, tmp.OT_HOURS, tmp.MEMBER_NAME, tmp.DEPARTMENT_NAME, CASE WHEN instr(tmp.减小时与不减, '.')= 0 THEN tmp.减小时与不减 WHEN substr(tmp.减小时与不减, instr(tmp.减小时与不减, '.')+ 1, 1)* 1 >= 5 THEN substr(tmp.减小时与不减, 1, instr(tmp.减小时与不减, '.')-1)+ 0.5 ELSE substr(tmp.减小时与不减, 1 , instr(tmp.减小时与不减, '.')-1)+ 0 END AS 判断 FROM ( SELECT t.开始, t.结束, round((t.结束-t.开始)* 24-1) AS 小时, t.OT_MINUTE, t.OT_HOURS, t.MEMBER_NAME, t.DEPARTMENT_NAME, CASE WHEN to_char(t.开始, 'HH24')* 1 <= 11 AND to_char(t.结束, 'HH24')* 1 >= 13 THEN (t.结束-t.开始)* 24-1 ELSE (t.结束-t.开始)* 24 END AS 减小时与不减 FROM ( SELECT S.OT_START_DATETIME / (1000 * 60 * 60 * 24)+ TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') AS 开始, S.OT_END_DATETIME / (1000 * 60 * 60 * 24)+ TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS') AS 结束, S.OVERTIME_TYPE, S.OT_MINUTE, S.OT_HOURS, M.MEMBER_NAME, M.DEPARTMENT_NAME FROM V3XUSER.ATT_OVERTIME_DETAILS S LEFT JOIN V3XUSER.ATT_MEMBER M ON S.MEMBER_ID = M.MEMBER_ID ) t ) tmp oracle的 在DBeaver上导出 SQL查询可以正常运行 在导出的时候报错 substr(tmp.减小时与不减, 1 , instr(tmp.减小时与不减, '.')-1)+ 0这一句开始报错 SQL 错误 [1722] [42000]: ORA-01722: 无效数字
|
1min目标场景问卷
立即参与
