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 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135
|
select count(*) from (
SELECT
decode(tab_result.typ, 2, FUNC.cc2,
37, FUNC.cc3) cc,
tab_result.nid ,
tab_result.sect ,
tab_result.nom ,
tab_result.codif ,
tab_result.m_sys,
tab_result.m_aer,
tab_result.m_tech,
tab_result.m_ll
FROM
(
SELECT
tabl.cle,
tabl.typ,
tabl.nid,
tabl.nom,
tabl.sect,
tabl.codif,
max(tabl.m_sys) m_sys,
max(tabl.m_aer) m_aer,
max(tabl.m_tech) m_tech,
max(tabl.m_ll) m_ll
FROM (
SELECT
N.cle,
N.typ,
N.nid,
N.nom,
N.sect,
D.codif,
null m_sys,
null m_aer,
null m_tech,
null m_ll
FROM MAT N INNER JOIN
SIM D ON substr(N.nid, -2) = D.codif
WHERE D.codifu = :param
and N.typ in (2,37)
UNION
SELECT
N1.cle,
N1.typ,
N1.nid,
N1.nom,
N1.sect,
D1.codif,
null m_sys,
null m_aer,
null m_tech,
CHARE_LL.CARAC_N2 m_ll
FROM MAT N1 LEFT OUTER JOIN
CHARE CHARE_LL ON N1.cle = CHARE_LL.cle INNER JOIN
SIM D1 ON substr(N1.nid, -2) = D1.codif
WHERE D1.codifu = :param AND (CHARE_LL.CARAC_N1 = 'Maintenance LL')
AND N1.typ in (2,37)
UNION
SELECT
N2.cle,
N2.typ,
N2.nid,
N2.nom,
N2.sect,
D2.codif,
null m_sys,
null m_aer,
CHARE_Tec.CARAC_N2 m_tech,
null m_ll
FROM MAT N2 LEFT OUTER JOIN
CHARE CHARE_Tec ON N2.cle = CHARE_Tec.cle INNER JOIN
SIM D2 ON substr(N2.nid, -2) = D2.codif
WHERE D2.codifu = :param AND (CHARE_Tec.CARAC_N1 = 'Maintenance Environnement Technique')
and N2.typ in (2,37)
UNION
SELECT
N3.cle,
N3.typ,
N3.nid,
N3.nom,
N3.sect,
D3.codif,
null m_sys,
CHARE_Aer.CARAC_N2 m_aer,
null m_tech,
null m_ll
FROM MAT N3 LEFT OUTER JOIN
CHARE CHARE_Aer ON N3.cle = CHARE_Aer.cle INNER JOIN
SIM D3 ON substr(N3.nid, -2) = D3.codif
WHERE D3.codifu = :param AND (CHARE_Aer.CARAC_N1 = 'Maintenance des Aériens')
and N3.typ in (2,37)
UNION
SELECT
N4.cle,
N4.typ,
N4.nid,
N4.nom,
N4.sect,
D4.codif,
CHARE_Syst.CARAC_N2 m_sys,
null m_aer,
null m_tech,
null m_ll
FROM MAT N4 LEFT OUTER JOIN
CHARE CHARE_Syst ON N4.cle = CHARE_Syst.cle INNER JOIN
SIM D4 ON substr(N4.nid, -2) = D4.codif
WHERE D4.codifu = :param AND (CHARE_Syst.CARAC_N1 = 'Maintenance Système')
and N4.typ in (2,37)
) tabl inner join
MAT_state on MAT_state.cle = tabl.cle
where MAT_state.STATE_KEY = 3
group by
tabl.cle,
tabl.typ,
tabl.nid,
tabl.nom,
tabl.sect,
tabl.codif
having (max(tabl.m_aer) is null or max(tabl.m_sys) is null
or max(tabl.m_tech) is null or max(tabl.m_ll) is null)
order by tabl.cle
) tab_result inner join
FUNC on tab_result.cle = FUNC.cle and tab_result.typ = FUNC.typ
) |
Partager