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
| SELECT DISTINCT
TABLE_P.LIBELLE,
TABLE_P.CODE,
TABLE_DOP.CODE,
TABLE_R.LIBELLE,
TABLE_R.STATUT_RESSOURCE,
TABLE_DPO.CODE,
(case substring(TABLE_LOP.CODE,1,4)
when 'ETUD' then substring(TABLE_LOP.CODE,1,4)
when 'DEVE' then substring(TABLE_LOP.CODE,1,4)
when 'RECC' then substring(TABLE_LOP.CODE,1,4)
when 'MURI' then substring(TABLE_LOP.CODE,1,4)
else TABLE_LOP.CODE
end),
TABLE_LOP.L_COURT,
TABLE_LOP.L_LONG,
TABLE_I.C,
TABLE_ICA.A,
TABLE_P.NCP,
TABLE_PH.L_LONG,
TABLE_AC.L_LONG,
TABLE_T.L_LONG,
TABLE_JAP.J_1_R,
TABLE_JAP.J0_R
TABLE_JAP.J2_R
TABLE_AVC.NA,
TABLE_AVC.C,
TABLE_P.A,
left(TABLE_P.CODE,1),
TABLE_P.TYPE_P,
TABLE_T.C,
( sum( TABLE_PPP.C_01 + TABLE_PPP.RAF_01 + TABLE_PPP.C_02 +
TABLE_PPP.RAF_02 + TABLE_PPP.C_03 + TABLE_PPP.RAF_03 + TABLE_PPP.C_04
+ TABLE_PPP.RAF_04 + TABLE_PPP.C_05 + TABLE_PPP.RAF_05 +
TABLE_PPP.C_06 + TABLE_PPP.RAF_06 + TABLE_PPP.C_07 + TABLE_PPP.RAF_07
+ TABLE_PPP.C_08 + TABLE_PPP.RAF_08 + TABLE_PPP.C_09 +
TABLE_PPP.RAF_09 + TABLE_PPP.C_10 + TABLE_PPP.RAF_10 + TABLE_PPP.C_11
+ TABLE_PPP.RAF_11 + TABLE_PPP.C_12 + TABLE_PPP.RAF_12) ) / 20,
( sum( TABLE_PPP.C_01 + TABLE_PPP.C_02 + TABLE_PPP.C_03 +
TABLE_PPP.C_04 + TABLE_PPP.C_05 + TABLE_PPP.C_06 + TABLE_PPP.C_07 +
TABLE_PPP.C_08 + TABLE_PPP.C_09 + TABLE_PPP.C_10 + TABLE_PPP.C_11 +
TABLE_PPP.C_12) ) / 20,
( sum( TABLE_PPP.RAF_01 + TABLE_PPP.RAF_02 + TABLE_PPP.RAF_03 +
TABLE_PPP.RAF_04 + TABLE_PPP.RAF_05 + TABLE_PPP.RAF_06 +
TABLE_PPP.RAF_07 + TABLE_PPP.RAF_08 + TABLE_PPP.RAF_09 +
TABLE_PPP.RAF_10 + TABLE_PPP.RAF_11 + TABLE_PPP.RAF_12) ) / 20,
sum( TABLE_PPP.C_01 + TABLE_PPP.C_02 + TABLE_PPP.C_03 +
TABLE_PPP.C_04 + TABLE_PPP.C_05 + TABLE_PPP.C_06 + TABLE_PPP.C_07 +
TABLE_PPP.C_08 + TABLE_PPP.C_09 + TABLE_PPP.C_10 + TABLE_PPP.C_11 +
TABLE_PPP.C_12),
sum( TABLE_PPP.RAF_01 + TABLE_PPP.RAF_02 + TABLE_PPP.RAF_03 +
TABLE_PPP.RAF_04 + TABLE_PPP.RAF_05 + TABLE_PPP.RAF_06 +
TABLE_PPP.RAF_07 + TABLE_PPP.RAF_08 + TABLE_PPP.RAF_09 +
TABLE_PPP.RAF_10 + TABLE_PPP.RAF_11 + TABLE_PPP.RAF_12),
sum( TABLE_PPP.REF_01 + TABLE_PPP.REF_02 + TABLE_PPP.REF_03 +
TABLE_PPP.REF_04 + TABLE_PPP.REF_05 + TABLE_PPP.REF_06 +
TABLE_PPP.REF_07 + TABLE_PPP.REF_08 + TABLE_PPP.REF_09 +
TABLE_PPP.REF_10 + TABLE_PPP.REF_11 + TABLE_PPP.REF_12),
sum( TABLE_PPP.C_01 + TABLE_PPP.RAF_01 + TABLE_PPP.C_02 +
TABLE_PPP.RAF_02 + TABLE_PPP.C_03 + TABLE_PPP.RAF_03 + TABLE_PPP.C_04
+ TABLE_PPP.RAF_04 + TABLE_PPP.C_05 + TABLE_PPP.RAF_05 +
TABLE_PPP.C_06 + TABLE_PPP.RAF_06 + TABLE_PPP.C_07 + TABLE_PPP.RAF_07
+ TABLE_PPP.C_08 + TABLE_PPP.RAF_08 + TABLE_PPP.C_09 +
TABLE_PPP.RAF_09 + TABLE_PPP.C_10 + TABLE_PPP.RAF_10 + TABLE_PPP.C_11
+ TABLE_PPP.RAF_11 + TABLE_PPP.C_12 + TABLE_PPP.RAF_12)
FROM
TABLE_JAP RIGHT OUTER JOIN TABLE_P ON
(TABLE_JPL.PLANNING=TABLE_P.ID_PLANNING)
FULL OUTER JOIN TABLE_LOP ON
(TABLE_P.ID_PLANNING=TABLE_LOP.PLANNING)
FULL OUTER JOIN TABLE_PH ON
(TABLE_PH.LOT_OPERATIONNEL=TABLE_LOP.ID_LOT_OP)
FULL OUTER JOIN TABLE_AC ON (TABLE_AC.PHASE=TABLE_PH.ID_PHASE)
FULL OUTER JOIN TABLE_T ON (TABLE_T.ACTIVITE=TABLE_AC.ID_ACTIVITE)
FULL OUTER JOIN TABLE_PPP ON ( TABLE_PPP.TACHE=TABLE_T.ID_TACHE)
FULL OUTER JOIN TABLE_R ON
(TABLE_R.ID_RES= TABLE_PPP.RES)
...
... |
Partager