1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| WITH t AS (
SELECT 'indexa' ind, TRUNC(SYSDATE, 'RRRR') AS ladate, '' etat_avant, 'b' etat_apres FROM dual
UNION SELECT 'indexb', TRUNC(SYSDATE, 'RRRR')+1 AS ladate, '' etat_avant, 'b' etat_apres FROM dual
UNION SELECT 'indexc', TRUNC(SYSDATE, 'RRRR')+2 AS ladate, '' etat_avant, 'b' etat_apres FROM dual
UNION SELECT 'indexa', TRUNC(SYSDATE, 'RRRR')+3 AS ladate, 'b' etat_avant, 'c' etat_apres FROM dual
UNION SELECT 'indexa', TRUNC(SYSDATE, 'RRRR')+4 AS ladate, 'c' etat_avant, 'd' etat_apres FROM dual
)
SELECT t1.ind, t1.ladate date1, t2.ladate date2, t1.etat_apres AS etat1, t2.etat_apres etat2
FROM t t1, t t2
WHERE t1.ind = t2.ind (+)
AND t1.etat_apres = t2.etat_avant (+)
IND DATE1 DATE2 ETAT1 ETAT2
indexa 01/01/2010 04/01/2010 b c
indexa 04/01/2010 05/01/2010 c d
indexa 05/01/2010 d
indexb 02/01/2010 b
indexc 03/01/2010 b |
Partager