Discussion :

[Totorote]

Bonjour j'ai la requête suivante qui prend du temps , et je pense qu'il est possible de l'optimiser avec un with :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
WITH FLAG_ANO AS  (
             SELECT 
                   CASE WHEN TDER_RM_BILLABLE_AREA.DP_ID <> DM_PAT_EDG.DP_ID  THEN 'ANOMALIE' ELSE '' END AS FLAG_ANO_EDG,
                    CASE WHEN EM.SIRET <> SITE_SIRET.SIRET THEN 'ANOMALIE' ELSE '' END AS FLAG_ANO_SIRET,
                   EM.CDR AS EM_CDR
                FROM
                      DM_PAT_EDG  EDG_BILLAREA RIGHT OUTER JOIN ( 
                            WITH fl_has_edg AS  (
                             -- dit s'il existe au moins une piece affectee a un EDG sur l'etage
                               SELECT bl_id, fl_id, CASE WHEN nb_edg > 0 THEN 1 ELSE 0 END    has_edg
                               FROM (
                               SELECT aff.bl_id, aff.fl_id , SUM(NVL2(aff.dp_id,1,0)) AS nb_edg
                               FROM rm_billable_area aff
                               GROUP BY aff.bl_id, aff.fl_id
                                  )
                             )
                             SELECT affect.bl_id, affect.fl_id, affect.rm_id, affect.rm_name, affect.area, 
                                    affect.rm_cat, affect.rm_std, affect.dv_id, affect.dp_id, affect.prorate, affect.avail_stat_id,
                                    affect.date_last_transfer, affect.user_last_transfer, affect.area_chargable,
                                    date_last_calcul, affect.area_parking, affect.area_comn, affect.volumes, rm.vtc
                             FROM    rm_billable_area affect
                             INNER JOIN property     ON (property.pr_id = affect.bl_id)
                             INNER JOIN fl ON (fl.bl_id = property.pr_id AND fl.fl_id = affect.fl_id)
                             INNER JOIN rm ON (rm.bl_id = fl.bl_id AND rm.fl_id = fl.fl_id AND rm.rm_id = affect.rm_id)
                           -- On prend que les affections sur les batiments/etages actifs
                             WHERE    (fl.date_valid_to IS NULL OR fl.date_valid_to >SYSDATE)
                             AND (property.date_valid_to IS NULL OR property.date_valid_to >SYSDATE)
                             UNION ALL
                           -- repartition des murs et cloisons sur batiments/etage actifs
                            SELECT  fl_has_edg.bl_id, fl_has_edg.fl_id, 
                                   NULL AS rm_id, NULL AS rm_name, fl.area_remain AS area, NULL AS rm_cat, 
                                   'WALL' AS rm_std, NULL AS dv_id, NULL AS dp_id,
                                   CASE WHEN fl_has_edg.has_edg = 1 THEN 'FLOOR' ELSE 'BUILDING' END AS prorate,
                                   NULL AS avail_stat_id, NULL AS date_last_transfer, NULL AS user_last_transfer, 
                                   0 AS area_chargable, NULL AS date_last_calcul, 0 AS area_parking, 0 AS area_comn, '-1' AS volumes, -1 AS vtc
                            FROM                  fl_has_edg
                               INNER JOIN property ON (property.pr_id = fl_has_edg.bl_id)
                               INNER JOIN fl ON (fl.bl_id = property.pr_id AND fl.fl_id = fl_has_edg.fl_id)                            
                               WHERE                (fl.date_valid_to IS NULL OR fl.date_valid_to >SYSDATE)
                               AND (property.date_valid_to IS NULL OR property.date_valid_to >SYSDATE)
                               AND property.has_dwgs = 1
 
                                )  TDER_RM_BILLABLE_AREA ON (EDG_BILLAREA.DV_ID=TDER_RM_BILLABLE_AREA.DV_ID AND EDG_BILLAREA.DP_ID=TDER_RM_BILLABLE_AREA.DP_ID)
                                   INNER JOIN DM_PAT_BL ON (TDER_RM_BILLABLE_AREA.BL_ID=DM_PAT_BL.BL_ID)
                                   INNER JOIN EM ON (DM_PAT_BL.BL_ID=EM.BL_ID)
                                   LEFT  JOIN RC ON (EM.CDR=RC.RC_ID)
                                   LEFT  JOIN DM_PAT_EDG ON (RC.DV_ID=DM_PAT_EDG.DV_ID and RC.DP_ID = DM_PAT_EDG.DP_ID)
 
                            )
    SELECT  EMP_CDR,
                FLAG_ANO_EDG,
                FLAG_ANO_SIRET
FROM FLAG_ANO;

Voici l'erreur que j'ai :


Merci d'avance pour votre aide