1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
| SELECT COALESCE(S3N.SB_NAME, ' ') AS "Opération",
MIN(CASE
WHEN C.CHAR_NAME = 'DATE DEBUT' THEN
SYS_EXTRACT_UTC(FROM_tz(CAST(TO_DATE(TB.CHAR_VALUE, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Paris')
ELSE
NULL
END) AS "Date début",
MAX(CASE
WHEN C.CHAR_NAME = 'DATE FIN' THEN
SYS_EXTRACT_UTC(FROM_tz(CAST(TO_DATE(TB.CHAR_VALUE, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/Paris')
ELSE
NULL
END) AS "Date fin",
FROM SUBBATCH_DATA D
JOIN SUBBATCHES S
ON D.SUBBATCH_ID = S.SUBBATCH_ID
LEFT OUTER JOIN SB2_NAMES S2N
ON S.SB2_INST_ID = S2N.SB2_NAME_ID
LEFT OUTER JOIN SB3_NAMES S3N
ON S.SB3_INST_ID = S3N.SB3_NAME_ID
LEFT OUTER JOIN SB4_NAMES S4N
ON S.SB4_INST_ID = S4N.SB4_NAME_ID
JOIN TIME_BATCH_DATA TB
ON D.BATCH_ID = TB.BATCH_ID
AND D.SUBBATCH_ID = TB.SUBBATCH_ID
JOIN CHARACTERISTICS C
ON TB.CHAR_ID = C.CHAR_ID
WHERE D.BATCH_ID = [ F#BATCH_ID ]
AND SB_LEVEL = 3
GROUP BY SB_LEVEL, S2N.SB_NAME, S3N.SB_NAME, S4N.SB_NAME
ORDER BY "Date début", SB_LEVEL |
Partager