ORA-01861: ???????????
WITH SortedData AS (
SELECT
A.equipmentid,
A.loadport,
TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS') AS txntimestamp,
A.state,
A.loadedfoup,
A.bindingfoup,
LAG(A.state, 1) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS')
) AS prev_state,
LAG(TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS'), 1) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS')
) AS prev_timestamp,
LAG(A.loadedfoup, 1) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS')
) AS prev_loadedfoup,
LAG(A.state, 2) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS')
) AS prev2_state,
LAG(A.bindingfoup, 2) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS')
) AS prev2_bindingfoup,
LAG(TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS'), 2) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS')
) AS prev2_timestamp,
LAG(A.state, 3) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS')
) AS prev3_state,
LAG(TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS'), 3) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS')
) AS prev3_timestamp,
LAG(A.state, 4) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS')
) AS prev4_state,
LAG(TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS'), 4) OVER (
PARTITION BY A.equipmentid, A.loadport
ORDER BY TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS')
) AS prev4_timestamp
FROM DR01.sdb_tb_smic_loadport_history A
JOIN SDB_TB_DATE_SHIFT B
ON TO_DATE(SUBSTR(A.txntimestamp, 1, 14), 'YYYYMMDDHH24MISS') BETWEEN B.starttime AND B.endtime
AND B.SHIFT_DATE >= TO_CHAR(SYSDATE-1,'YYYYMMDD')
WHERE A.equipmentid IN ('EDPTC01')
AND A.state IS NOT NULL
),
FilteredData AS (
SELECT
equipmentid,
loadport,
txntimestamp AS time4,
prev_timestamp AS time3,
prev2_timestamp AS time2,
CASE
WHEN prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToLoad'
THEN prev3_timestamp
WHEN prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToUnload'
THEN prev2_timestamp
ELSE CAST(NULL AS DATE)
END AS time1,
CASE
WHEN prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToLoad'
THEN prev4_timestamp
WHEN prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToUnload'
THEN prev3_timestamp
ELSE CAST(NULL AS DATE)
END AS time0,
loadedfoup
FROM SortedData
WHERE
state = 'ReadyToUnload'
AND prev_state = 'LoadCompleted'
AND prev_loadedfoup = loadedfoup
AND (
(prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToLoad' AND prev2_bindingfoup = loadedfoup)
OR
(prev2_state = 'ReadyToLoad' AND prev3_state = 'ReadyToUnload' AND prev2_bindingfoup = loadedfoup)
)
AND prev4_state = 'ReadyToUnload'
AND prev3_timestamp IS NOT NULL
AND prev4_timestamp IS NOT NULL
)
SELECT
equipmentid,
loadedfoup AS foupid,
loadport,
TO_CHAR(time0, 'YYYYMMDDHH24MISS') AS time0,
TO_CHAR(time1, 'YYYYMMDDHH24MISS') AS time1,
TO_CHAR(time2, 'YYYYMMDDHH24MISS') AS time2,
TO_CHAR(time3, 'YYYYMMDDHH24MISS') AS time3,
TO_CHAR(time4, 'YYYYMMDDHH24MISS') AS time4,
ROUND((time1 - time0) * 86400) AS time10,
ROUND((time2 - time1) * 86400) AS time21,
ROUND((time3 - time2) * 86400) AS time32,
ROUND((time4 - time3) * 86400) AS time43
FROM FilteredData
WHERE time0 IS NOT NULL
AND time1 IS NOT NULL
AND time2 IS NOT NULL
AND time3 IS NOT NULL
AND time4 IS NOT NULL
ORDER BY equipmentid, loadport, time4
输出完整的修改后的代码
最新发布
本文介绍了一种使用Java进行时间戳格式化的方法，具体格式为yyyyMMddHHmmss。通过SimpleDateFormat类结合System.currentTimeMillis()实现当前时间的时间戳格式化。
扫一扫
4661
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
