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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75
| SELECT case a.succes
WHEN 100 THEN 'SUCCES'
END as status ,
COUNT (*) AS nombre,TRUNC( COUNT (*) *100/ ( select count(*) from report_backup a, spec_dest b
WHERE a.specification_n = b.specification_n
AND ( a.specification_n LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
AND a.specification_n LIKE '%Oracle8%'
AND a.TYPE_SESSION = 'Backup'
and (a.starttime > (sysdate - 1))),2) || ' %' as pourcentage
FROM report_backup a, spec_dest b
WHERE a.specification_n = b.specification_n
AND ( a.specification_n LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
AND a.specification_n LIKE '%Oracle8%'
and succes = '100'
AND a.TYPE_SESSION = 'Backup'
and (a.starttime > (sysdate - 1))
GROUP BY ( CASE a.succes
WHEN 100 THEN 'SUCCES'
END )
UNION
SELECT
case a.STATUS
WHEN 'In Progress' THEN 'EN COURS'
WHEN 'In Progress/Errors' THEN 'EN COURS'
else 'ECHEC'
END as status ,
COUNT (*) AS nombre,TRUNC( COUNT (*) *100/ ( select count(*) from report_backup a, spec_dest b
WHERE a.specification_n = b.specification_n
AND ( a.specification_n LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
AND a.specification_n LIKE '%Oracle8%'
AND a.TYPE_SESSION = 'Backup'
and (a.starttime > (sysdate - 1))),2) || ' %' as pourcentage
FROM report_backup a, spec_dest b
WHERE a.specification_n = b.specification_n
AND ( a.specification_n LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
AND a.specification_n LIKE '%Oracle8%'
and succes <> '100'
AND a.TYPE_SESSION = 'Backup'
and (a.starttime > (sysdate - 1))
and not exists (select * from report_backup t2 where succes = 100
and (t2.starttime >(SYSDATE -1))
and (a.specification_n=t2.specification_n) and (t2.starttime > a.starttime))
GROUP BY ( case a.STATUS
WHEN 'In Progress' THEN 'EN COURS'
WHEN 'In Progress/Errors' THEN 'EN COURS'
else 'ECHEC'
END )
UNION
SELECT
case a.STATUS
WHEN 'In Progress' THEN 'EN COURS'
WHEN 'In Progress/Errors' THEN 'EN COURS'
else 'RELANCE AVEC SUCCES'
END as status ,
COUNT (*) AS nombre,TRUNC( COUNT (*) *100/ ( select count(*) from report_backup a, spec_dest b
WHERE a.specification_n = b.specification_n
AND ( a.specification_n LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
AND a.specification_n LIKE '%Oracle8%'
AND a.TYPE_SESSION = 'Backup'
and (a.starttime > (sysdate - 1))),2) || ' %' as pourcentage
FROM report_backup a, spec_dest b
WHERE a.specification_n = b.specification_n
AND ( a.specification_n LIKE 'Oracle%ONLINE%' or a.specification_n like 'Oracle%AREA%' )
AND a.specification_n LIKE '%Oracle8%'
and succes <> '100'
AND a.TYPE_SESSION = 'Backup'
and (a.starttime > (sysdate - 1))
and exists (select * from report_backup t2 where succes = 100
and (t2.starttime >(SYSDATE -1))
and (a.specification_n=t2.specification_n) and (t2.starttime > a.starttime))
GROUP BY ( case a.STATUS
WHEN 'In Progress' THEN 'EN COURS'
WHEN 'In Progress/Errors' THEN 'EN COURS'
else 'RELANCE AVEC SUCCES'
END ); |
Partager