Hello All,
I am a newbie here. I am looking for some help to resolve an issue with the Oracle SQL query. I am trying to resolve the invalid Number error for the last three days. Here is the query syntax.
SELECT ROW_NUMBER() OVER (ORDER BY es.evstrname, e.eta) As SrNo, s.LDATE, es.EVSTRNAME, e.ACTIVITY,
e.TRAVELTIME, b.directdistance, e.DWELLTIME, e.ETD,
TO_CHAR (TRUNC (SYSDATE) + numtodsinterval(DECODE( e.actualarrivetime, -1, 0, e.actualarrivetime), 'second'),'hh24:mi') ACTUAL_arriveTime,
to_char(Trunc(sysdate)+numtodsinterval(DECODE ((( e.actualarrivetime - ( lag(e.actualarrivetime)
OVER (partition by es.evstrname ORDER BY es.evstrname, e.eta)))),-1, 0,( e.actualarrivetime - ( lag(e.actualarrivetime)
OVER (partition by es.evstrname ORDER BY es.evstrname, e.eta))),'second'),'hh24:mi')) as diff2
FROM (((EVENTSTRINGS es INNER JOIN EVENTS e ON (es.SCHID=e.SCHID)
AND (es.EVSTRID=e.EVSTRID)) LEFT OUTER JOIN Employees em ON es.EMPLOYEEID=em.EMPLOYEEID)
INNER JOIN Schedules s ON e.SCHID=s.SCHID left outer join booking b on e.bookingid=b.bookingid)
WHERE s.LDATE=20180913 ORDER BY s.LDATE, es.EVSTRNAME, e.ESTTIME
I tried to write the same syntax in a different way but no luck. Here is the second syntax:
SELECT ROW_NUMBER() OVER (ORDER BY es.evstrname, e.eta) As SrNo, s.LDATE, es.EVSTRNAME, e.ACTIVITY,
e.TRAVELTIME, b.directdistance, e.DWELLTIME, e.ETD,
TO_CHAR (TRUNC (SYSDATE) + numtodsinterval(DECODE( e.actualarrivetime, -1, 0, e.actualarrivetime), 'second'),'hh24:mi') ACTUAL_arriveTime,
(TO_CHAR (TRUNC (SYSDATE) + numtodsinterval(DECODE( e.actualdeparttime, -1, 0, e.actualdeparttime), 'second'),'hh24:mi')-
lag (TO_CHAR (TRUNC (SYSDATE) + numtodsinterval(DECODE( e.actualdeparttime, -1, 0, e.actualdeparttime), 'second'),'hh24:mi'))
OVER (partition by es.evstrname ORDER BY es.evstrname, e.eta)) as diff2
FROM (((EVENTSTRINGS es INNER JOIN EVENTS e ON (es.SCHID=e.SCHID)
AND (es.EVSTRID=e.EVSTRID)) LEFT OUTER JOIN Employees em ON es.EMPLOYEEID=em.EMPLOYEEID)
INNER JOIN Schedules s ON e.SCHID=s.SCHID left outer join booking b on e.bookingid=b.bookingid)
WHERE s.LDATE=20180913 ORDER BY s.LDATE, es.EVSTRNAME, e.ESTTIME
I would appreciate if someone from the community will help me. Thanks