Whenever I compile both the query It displays error kindly help me to resolve

Question

Below mentioned query is not compiling in Oracle Sql what is wrong with the query

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, 'ddd') as dayName1,  
      TO_CHAR(ShiftStartTime,'hh:mm tt') ShiftStartTime, TO_CHAR(ShiftEndTime,'hh:mm tt') ShiftEndTime, 
      TO_CHAR((SignOut,SignIn)/60 )|| ' Hrs' || ':'|| SUBSTR('0' + TO_CHAR( (SignOut,SignIn)%60)(2)), 
      GREATEST(-LENGTH('0' + TO_CHAR(DATEDIFF(SignOutMinute -SignIn, SignOut)%60 AS VARCHAR2(2))), -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 a.LoginId = case when nvl(V_LoginId,'') = '' then a.LoginId else V_LoginId end  
      order by A.AttendanceDate ASC




--------------------------------------------------------------------------------------------------------------------------------------

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, 'ddd') as dayName1,  SignIn, SignOut, 
        TO_CHAR( (SignOut -SignIn) * 1440/60 (5))|| ' Hrs' || ':'|| SUBSTR('0' + TO_CHAR( (SignOut -SignIn) * 1440%60 (2)), GREATEST(-LENGTH('0' + TO_CHAR( DATEDIFF(SignOutMinute -SignIn, SignOut) * 1440%60 AS VARCHAR2(2))), -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 uat.TowerPK =
        case when nvl(V_Tower,'') = '' then '' else V_Tower end  
        and A.Active = 1
        AND ATTENDANCEDATE= V_AttendanceDate

Answer 1

Your date format models are wrong: MMM should be MON, hh:mm tt should be HH24:MI or HH12:MI AM, if you want the day name then ddd should be day (or fmday).

You also need to fix the duration code as DATEDIFF should just be subtraction and Oracle has a MOD function (not the % operator); however, you can simplify that problem using EXTRACT:

SELECT AttendanceId,
       E.FIRSTNAME || ' ' || E.LASTNAME AS FullName,
       E.EmpID,
       E.JobCode,
       E.Address as Location,
       E.VSMS_Id,
       A.LoginId,
       upper(ShiftName) AS ShiftName,
       To_char(AttendanceDate, 'dd-mon-yy') AS AttendanceDate, 
       to_char(AttendanceDate, 'day') AS dayName1,  
       TO_CHAR(ShiftStartTime,'hh24:mi') AS ShiftStartTime, 
       TO_CHAR(ShiftEndTime,'hh24:mi') AS ShiftEndTime,
       EXTRACT(HOUR FROM (signout - signin) DAY TO SECOND) || ' Hrs'
       || ':'
       || LPAD(EXTRACT(MINUTE FROM (signout - signin) DAY TO SECOND), 2, '0') ||' 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  ATTENDANCEDATE >= TO_DATE(V_YEAR||'-'||V_MONTH||'-01', 'YYYY-MM-DD')
AND    ATTENDANCEDATE <  TO_DATE((V_YEAR+1)||'-'||V_MONTH||'-01', 'YYYY-MM-DD')
AND    A.active = 1  
And    (a.LoginId = V_LoginId OR v_loginid IS NULL) 
order by A.AttendanceDate ASC