Outer join to select - Oracle - Ответ 7222722

SELECT Reg AS Region, 
       COUNT (Reg) AS Sleeping  
FROM 
     (SELECT DECODE_SITE_TO_REGION(sel1.BSC_NUMBER,sel1.segment_id) AS reg
      FROM 
           (SELECT * 
            FROM Re.Bsc_Counter_2_Stat_Kpi
            WHERE TRF_202>0.5 AND period_real_start_time >= trunc(sysdate-1/24, 'HH') 
            ORDER BY PERIOD_REAL_START_TIME DESC
           ) sel1,
           (SELECT * 
            FROM Re.Bsc_Counter_79_Stat_Kpi
            WHERE Period_Real_Start_Time >= Trunc(Sysdate-1/24, 'HH') 
            ORDER BY Period_Real_Start_Time DESC
           ) Sel2
      WHERE sel1.BSC_NUMBER=sel2.BSC_NUMBER(+)
        AND sel1.SEGMENT_ID=sel2.SEGMENT_ID(+)
        AND sel1.PERIOD_REAL_START_TIME=sel2.PERIOD_REAL_START_TIME(+)
        AND (Sel2.Trf_215a=0 OR Sel2.Trf_215a IS NULL)
      ORDER BY Sel1.Period_Real_Start_Time DESC,
               Sel1.Bsc_Number,
               Sel1.Segment_Id
     )
GROUP BY Reg 
ORDER BY sleeping DESC;

SELECT s1.Reg AS Region, 
       COUNT (Reg) AS Sleeping  
FROM 
       (SELECT DECODE_SITE_TO_REGION(sel1.BSC_NUMBER,sel1.segment_id) AS reg
        FROM
               (SELECT * 
                FROM Re.Bsc_Counter_2_Stat_Kpi
                WHERE TRF_202>0.5 AND period_real_start_time >= trunc(sysdate-1/24, 'HH') 
                ORDER BY PERIOD_REAL_START_TIME DESC
               ) sel1,
               (SELECT * 
                FROM Re.Bsc_Counter_79_Stat_Kpi
                WHERE Period_Real_Start_Time >= Trunc(Sysdate-1/24, 'HH') 
                ORDER BY Period_Real_Start_Time DESC
               ) Sel2
          WHERE sel1.BSC_NUMBER=sel2.BSC_NUMBER(+)
            AND sel1.SEGMENT_ID=sel2.SEGMENT_ID(+)
            AND sel1.PERIOD_REAL_START_TIME=sel2.PERIOD_REAL_START_TIME(+)
            AND (Sel2.Trf_215a=0 OR Sel2.Trf_215a IS NULL)
       ORDER BY Sel1.Period_Real_Start_Time DESC,
                Sel1.Bsc_Number,
                Sel1.Segment_Id
       ) S1
LEFT OUTER JOIN (SELECT DISTINCT Site_Region 
                 FROM Site_To_Site_Address
                ) S2
             ON s1.reg=s2.site_region
GROUP BY Reg 
ORDER BY sleeping DESC;