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
| WITH cpt_entite AS
(SELECT COUNT (DISTINCT id_entite) AS nb_wait,
TRIM (type_entite) AS typetrim,
INTERFACE,
statut
FROM entite
WHERE INTERFACE LIKE 'NG%'
GROUP BY INTERFACE, TRIM (type_entite), statut
)
SELECT entite.INTERFACE, TRIM (entite.type_entite) AS expr1,
tab_wait.nb_wait, tab_send.nb_send, tab_err.nb_err, tab_arc.nb_arc,
MAX (entite.creation) AS LAST
FROM entite
FULL OUTER JOIN
(SELECT nb_wait,
typetrim, INTERFACE
FROM cpt_entite
WHERE (statut = 0)) tab_wait
ON TRIM (entite.type_entite) = tab_wait.typetrim
AND entite.INTERFACE = tab_wait.INTERFACE
FULL OUTER JOIN
(SELECT nb_wait,
typetrim, INTERFACE
FROM cpt_entite
WHERE (statut = 9)) tab_wait
ON TRIM (entite.type_entite) = tab_wait.typetrim
AND entite.INTERFACE = tab_wait.INTERFACE
FULL OUTER JOIN
(SELECT nb_wait,
typetrim, INTERFACE
FROM cpt_entite
WHERE (statut = 0)) tab_wait
ON TRIM (entite.type_entite) = tab_err.typetrim
AND entite.INTERFACE = tab_err.INTERFACE
FULL OUTER JOIN
(SELECT nb_wait,
typetrim, INTERFACE
FROM cpt_entite
WHERE (statut = 8)) tab_arc
ON TRIM (entite.type_entite) = tab_arc.typetrim
AND entite.INTERFACE = tab_arc.INTERFACE
GROUP BY entite.INTERFACE,
TRIM (entite.type_entite),
tab_wait.nb_wait,
tab_send.nb_send,
tab_err.nb_err,
tab_arc.nb_arc
ORDER BY entite.INTERFACE |
Partager