Oracle SQL查询编译报错求助：请排查语句问题并解决

查询一：错误分析与修正 #

核心语法问题 #

• 时间差计算写法错误：使用(SignOut,SignIn)逗号分隔参数，Oracle中日期相减需用SignOut - SignIn得到天数差。
• 不存在DATEDIFF函数：Oracle无此函数，日期直接相减得到天数，转换为小时/分钟需乘以对应系数（1天=24小时=1440分钟）。
• 字符串拼接符错误：用+拼接字符串，Oracle中正确拼接符为||。
• 取模运算符错误：用%做取模运算，Oracle需使用MOD()函数。
• SUBSTR语法错误：SUBSTR('0' + TO_CHAR(...)(2))格式错误，应指定起始位置为-2来截取最后两位。
• CASE逻辑冗余：原a.LoginId = case when nvl(V_LoginId,'') = '' then a.LoginId else V_LoginId end可简化为V_LoginId IS NULL OR A.LoginId = V_LoginId。

修正后的查询语句 #

SELECT 
    AttendanceId,
    E.FIRSTNAME || ' ' || E.LASTNAME AS FullName,
    E.EmpID,
    E.JobCode,
    E.Address as Location,
    E.VSMS_Id,
    A.LoginId,
    UPPER(ShiftName) ShiftName,
    TO_CHAR(AttendanceDate, 'dd-MMM-yy') AttendanceDate,
    TO_CHAR(AttendanceDate, 'DAY') as dayName1, -- 原'ddd'返回一年中的第几天，若要星期名用'DAY'或'DY'
    TO_CHAR(ShiftStartTime,'hh:mm tt') ShiftStartTime,
    TO_CHAR(ShiftEndTime,'hh:mm tt') ShiftEndTime,
    -- 计算时长：提取小时和分钟，补零对齐
    TO_CHAR(FLOOR((SignOut - SignIn)*24)) || ' Hrs' || ':' || 
    SUBSTR('0' || TO_CHAR(MOD((SignOut - SignIn)*24*60, 60)), -2) || ' Min' as Duration,
    SignIn,
    SignOut,
    Remark
FROM T_ATTENDANCE_ATTENDANCE A
LEFT JOIN USERMASTER U ON U.LOGINID = A.LOGINID
LEFT JOIN EMPLOYEEMASTER E ON E.EMPID = U.EMPID
WHERE 
    EXTRACT(YEAR FROM ATTENDANCEDATE) = V_YEAR 
    AND EXTRACT(MONTH FROM ATTENDANCEDATE) = V_MONTH
    AND A.active = 1
    AND (V_LoginId IS NULL OR A.LoginId = V_LoginId)
ORDER BY A.AttendanceDate ASC

查询二：错误分析与修正 #

核心语法问题 #

• TO_CHAR格式参数位置错误：TO_CHAR( (SignOut -SignIn) * 1440/60 (5))中(5)位置无效，正确格式为TO_CHAR(数值, '格式')。
• 同样存在DATEDIFF函数误用：需用日期相减乘以系数计算时间差。
• 取模运算符错误：用%做取模，Oracle需使用MOD()函数。
• 字符串拼接符错误：用+拼接字符串，应改为||。
• JOIN条件空值判断逻辑错误：uat.TowerPK = case when nvl(V_Tower,'') = '' then '' else V_Tower end中，Oracle中空字符串等价于NULL，导致V_Tower为空时无匹配，需改为V_Tower IS NULL OR uat.TowerPK = V_Tower。
• JOIN与WHERE条件混淆：A.Active = 1放在JOIN条件中会将LEFT JOIN转为INNER JOIN，需移至WHERE子句。

修正后的查询语句 #

SELECT 
    E.FIRSTNAME || ' ' || E.LASTNAME AS FullName,
    E.EmpID,
    E.JobCode,
    E.Address as Location,
    E.VSMS_Id,
    A.LoginId,
    UPPER(ShiftName) ShiftName,
    TO_CHAR(AttendanceDate, 'dd-MMM-yy') AttendanceDate,
    TO_CHAR(AttendanceDate, 'DAY') as dayName1, -- 同上，若需星期名修改格式
    SignIn,
    SignOut,
    -- 计算时长：提取小时和分钟，补零对齐
    TO_CHAR(FLOOR((SignOut - SignIn)*24)) || ' Hrs' || ':' || 
    SUBSTR('0' || TO_CHAR(MOD((SignOut - SignIn)*24*60, 60)), -2) || ' Min' as Duration,
    CASE 
        WHEN UPPER(ShiftName) = 'MORNING' THEN 1
        WHEN UPPER(ShiftName) = 'GENERAL1' THEN 2
        WHEN UPPER(ShiftName) = 'GENERAL2' THEN 3
        WHEN UPPER(ShiftName) = 'AFTERNOON1' THEN 4
        WHEN UPPER(ShiftName) = 'AFTERNOON2' THEN 5
        WHEN UPPER(ShiftName) = 'NIGHT' THEN 6
        ELSE 7 
    END ShiftOrder
FROM T_ATTENDANCE_ATTENDANCE A
LEFT JOIN T_ATTENDANCE_USER_MASTER U ON U.LOGINID = A.LOGINID
LEFT JOIN T_ATTENDANCE_EMPLOYEE_MASTER E ON E.EMPID = U.EMPID
JOIN T_ATTENDANCE_USER_ATTENDANCE_TEAM uat 
    ON uat.LoginId = U.LoginID 
    AND uat.isActive = 'Y'
    AND (V_Tower IS NULL OR uat.TowerPK = V_Tower)
WHERE 
    A.Active = 1
    AND ATTENDANCEDATE = V_AttendanceDate

内容的提问来源于stack exchange，提问作者sudhir mishra