4 Replies Latest reply: Mar 20, 2014 3:04 AM by michaelrozar17 RSS

    Query taking  time

    surendra4y

      Hi All ,

      Below  query is taking  too much time  as  i have  created  proper  indexs  and  gether    stat

       

      in  my  FACT_PASSENGER we  have  2000000 recodrd  but  in  other  table  we  have  only 50 to 60 thousend  row.

       

         select sum(case  when T6214.RBD_REV_FLAG <> 'Y' and T6472.CHILD_FLAG = 'N' and T6472.PNR_NUMBER <> 'CREW' and T6472.FLOWN_PAX_FLAG = 'Y' and (T6472.PAX_GENDER in ('F', 'M')) then (nvl(T6472.PAX_FREE_WEIGHT , 0) + 77) * T536.LEG_DISTANCE / 1000000 when T6214.RBD_REV_FLAG <> 'Y' and T6472.CHILD_FLAG = 'Y' and T6472.FLOWN_PAX_FLAG = 'Y' and T6472.PNR_NUMBER <> 'CREW' then (nvl(T6472.PAX_FREE_WEIGHT , 0) + 40) * T536.LEG_DISTANCE / 1000000 when T6214.RBD_REV_FLAG <> 'Y' and T6472.INFANT_FLAG = 'Y' and T6472.FLOWN_PAX_FLAG = 'Y' and T6472.PNR_NUMBER <> 'CREW' then (nvl(T6472.PAX_FREE_WEIGHT , 0) + 10) * T536.LEG_DISTANCE / 1000000 else NULL end ) as c1,
                     count(distinct case  when T6472.XRES_FLAG = 'N' and T6472.PNR_NUMBER <> 'CREW' and T6214.RBD_REV_FLAG = 'Y' and T6214.CLASS_CODE = 'Y' and T6472.ACCEPTED_FLAG = 'Y' then T6472.PAX_SID else NULL end ) as c2,
                     count(distinct case  when T6472.XRES_FLAG = 'N' and T6472.PNR_NUMBER <> 'CREW' and T6214.RBD_REV_FLAG = 'Y' and T6214.CLASS_CODE = 'C' and T6472.ACCEPTED_FLAG = 'Y' then T6472.PAX_SID else NULL end ) as c3,
                     count(distinct case  when T6472.XRES_FLAG = 'N' and T6472.PNR_NUMBER <> 'CREW' and T6214.RBD_REV_FLAG = 'Y' and T6214.CLASS_CODE = 'F' and T6472.ACCEPTED_FLAG = 'Y' then T6472.PAX_SID else NULL end ) as c4,
                     count(distinct case  when T6472.XRES_FLAG = 'N' and T6472.PNR_NUMBER <> 'CREW' and T6214.RBD_REV_FLAG <> 'Y' and T6214.CLASS_CODE = 'Y' and T6472.FLOWN_PAX_FLAG = 'Y' then T6472.PAX_SID else NULL end ) as c5,
                     count(distinct case  when T6472.XRES_FLAG = 'N' and T6472.PNR_NUMBER <> 'CREW' and T6214.RBD_REV_FLAG <> 'Y' and T6214.CLASS_CODE = 'C' and T6472.FLOWN_PAX_FLAG = 'Y' then T6472.PAX_SID else NULL end ) as c6,
                     count(distinct case  when T6472.XRES_FLAG = 'N' and T6472.PNR_NUMBER <> 'CREW' and T6214.RBD_REV_FLAG <> 'Y' and T6214.CLASS_CODE = 'F' and T6472.FLOWN_PAX_FLAG = 'Y' then T6472.PAX_SID else NULL end ) as c7,
                     T263.AIRPORT_CODE as c8,
                     T283.AIRPORT_CODE as c9,
                     T519.FLIGHT_NUMBER as c10,
                     T242.AIRCRAFT_REG_NUMBER as c11,
                     T242.AIRCRAFT_TYPE as c12,
                     T548.ROUTE as c13,
                     T386.FULL_DATE as c14,
                     case  when T283.AIRPORT_CODE = substr(T548.ROUTE , 1, 3) then 1 when T283.AIRPORT_CODE = substr(T548.ROUTE , 5 , 3) then 2 when T283.AIRPORT_CODE = substr(T548.ROUTE , 9 , 3) then 3 when T283.AIRPORT_CODE = substr(T548.ROUTE , 13 , 3) then 4 end  as c15,
                     T386.DATE_SID as c16,
                     ROW_NUMBER() OVER (PARTITION BY T242.AIRCRAFT_REG_NUMBER, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T519.FLIGHT_NUMBER, T548.ROUTE ORDER BY T242.AIRCRAFT_REG_NUMBER ASC, T263.AIRPORT_CODE ASC, T283.AIRPORT_CODE ASC, T386.DATE_SID ASC, T519.FLIGHT_NUMBER ASC, T548.ROUTE ASC) as c17
                from
                     DIM_AIRCRAFT T242 /* Dim Aircraft */ ,
                     DIM_DATE T386 /* Dim Scheduled Departure Date */ ,
                     DIM_AIRPORT T263 /* Dim Arrival Airport */ ,
                     DIM_AIRPORT T283 /* Dim Departure Airport */ ,
                     DIM_FLIGHT T519 /* Dim Flight */ ,
                     DIM_LEG T536 /* Dim Leg */ ,
                     DIM_ROUTE_MV T548 /* Dim Route */ ,
                     DIM_CLASS T6214 /* Dim Class */ ,
                     (
                         
                               FACT_PASSENGER T6472 /* Fact Passenger */  left outer join BRIDGE_PAX_FLIGHT T6530 /* Fact Bridge Passenger Flight */  On T6472.PAX_SID = T6530.PAX_SID) left outer join BRIDGE_PAX_FLIGHT T48010 /* Dim Bridge Passenger Flight Inbound */  On T6472.PAX_SID = T48010.PAX_SID and T6472.FLIGHT_SID = T48010.OUTBOUND_FLIGHT_SID
                where  ( T242.AIRCRAFT_SID = T6472.AIRCRAFT_SID and
          T263.AIRPORT_SID = T6472.ARRIVAL_AIRPORT_SID and
          T263.AI_OPERATED_AIRPORT_FLAG = 'Y' and
          T283.AIRPORT_SID = T6472.DEPARTURE_AIRPORT_SID and
          T283.AI_OPERATED_AIRPORT_FLAG = 'Y' and
          T386.DATE_SID = T6472.DEPARTURE_DATE_SID and
          T519.FLIGHT_PREFIX = 'AI' and
          T519.FLIGHT_SID = T6472.FLIGHT_SID and
          T536.LEG_SID = T6472.LEG_SID and
          T548.ROUTE_SID = T6472.ROUTE_SID and
          T6214.RBD_CLASS_SID = T6472.RBD_CLASS_SID and
          T263.AIRPORT_SID <> -1 and
          T283.AIRPORT_SID <> -1 and
          T386.DATE_SID <> -1 and
          T519.FLIGHT_SID <> -1 and
          T548.ROUTE_SID <> -1 and
          T6472.ARRIVAL_AIRPORT_SID <> T6472.DEPARTURE_AIRPORT_SID and
          instr(T548.ROUTE , concat(concat(T283.AIRPORT_CODE, '-'), T263.AIRPORT_CODE)) <> 0 and T386.FULL_DATE between TO_DATE('2013-01-18' , 'YYYY-MM-DD') and TO_DATE('2013-02-18' , 'YYYY-MM-DD') )
                group by T242.AIRCRAFT_REG_NUMBER,
          T242.AIRCRAFT_TYPE,
          T263.AIRPORT_CODE,
          T283.AIRPORT_CODE,
          T386.DATE_SID,
          T386.FULL_DATE,
          T519.FLIGHT_NUMBER,
          T548.ROUTE
           ) D1
      where  ( D1.c17 = 1 ) ),
      SAWITH1 AS (select D1.c1 as c1,
           D1.c2 as c2,
           D1.c3 as c3,
           D1.c4 as c4,
           D1.c5 as c5,
           D1.c6 as c6,
           D1.c7 as c7,
           D1.c8 as c8,
           D1.c9 as c9,
           D1.c10 as c10,
           D1.c11 as c11
      from
           (select sum(T559.TOTAL_DELAY_TIME) as c1,
                     sum(T559.ACTUAL_BLOCK_TIME) as c2,
                     T263.AIRPORT_CODE as c3,
                     T283.AIRPORT_CODE as c4,
                     T519.FLIGHT_NUMBER as c5,
                     T242.AIRCRAFT_REG_NUMBER as c6,
                     T242.AIRCRAFT_TYPE as c7,
                     T548.ROUTE as c8,
                     T386.FULL_DATE as c9,
                     case  when T283.AIRPORT_CODE = substr(T548.ROUTE , 1, 3) then 1 when T283.AIRPORT_CODE = substr(T548.ROUTE , 5 , 3) then 2 when T283.AIRPORT_CODE = substr(T548.ROUTE , 9 , 3) then 3 when T283.AIRPORT_CODE = substr(T548.ROUTE , 13 , 3) then 4 end  as c10,
                     T386.DATE_SID as c11,
                     ROW_NUMBER() OVER (PARTITION BY T242.AIRCRAFT_REG_NUMBER, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T519.FLIGHT_NUMBER, T548.ROUTE ORDER BY T242.AIRCRAFT_REG_NUMBER ASC, T263.AIRPORT_CODE ASC, T283.AIRPORT_CODE ASC, T386.DATE_SID ASC, T519.FLIGHT_NUMBER ASC, T548.ROUTE ASC) as c12
                from
                     DIM_ROUTE_MV T548 /* Dim Route */ ,
                     DIM_AIRCRAFT T242 /* Dim Aircraft */ ,
                     DIM_DATE T386 /* Dim Scheduled Departure Date */ ,
                     DIM_AIRPORT T263 /* Dim Arrival Airport */ ,
                     DIM_AIRPORT T283 /* Dim Departure Airport */ ,
                     DIM_FLIGHT T519 /* Dim Flight */ ,
                     FACT_FLIGHT_OPS T559 /* Fact Flight Operations */
                where  ( T242.AIRCRAFT_SID = T559.AIRCRAFT_SID and T263.AIRPORT_SID = T559.ARRIVAL_AIRPORT_SID and T263.AI_OPERATED_AIRPORT_FLAG = 'Y' and T283.AIRPORT_SID = T559.DEPARTURE_AIRPORT_SID and T283.AI_OPERATED_AIRPORT_FLAG = 'Y' and T386.DATE_SID = T559.SCHEDULE_DEP_DATE_SID and T519.FLIGHT_PREFIX = 'AI' and T519.FLIGHT_SID = T559.FLIGHT_SID and T548.ROUTE_SID = T559.ROUTE_SID and T263.AIRPORT_SID <> -1 and T283.AIRPORT_SID <> -1 and T386.DATE_SID <> -1 and T519.FLIGHT_SID <> -1 and T548.ROUTE_SID <> -1 and T559.ARRIVAL_AIRPORT_SID <> T559.DEPARTURE_AIRPORT_SID and instr(T548.ROUTE , concat(concat(T283.AIRPORT_CODE, '-'), T263.AIRPORT_CODE)) <> 0 and T386.FULL_DATE between TO_DATE('2013-01-18' , 'YYYY-MM-DD') and TO_DATE('2013-02-18' , 'YYYY-MM-DD') )
                group by T242.AIRCRAFT_REG_NUMBER, T242.AIRCRAFT_TYPE, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T386.FULL_DATE, T519.FLIGHT_NUMBER, T548.ROUTE
           ) D1
      where  ( D1.c12 = 1 ) ),
      SAWITH2 AS (select D1.c1 as c1,
           D1.c2 as c2,
           D1.c3 as c3,
           D1.c4 as c4,
           D1.c5 as c5,
           D1.c6 as c6,
           D1.c7 as c7,
           D1.c8 as c8,
           D1.c9 as c9,
           D1.c10 as c10,
           D1.c11 as c11,
           D1.c12 as c12
      from
           (select sum(T36562.FIRST_CLASS_OVER_BOOK_CAPACITY) as c1,
                     sum(T36562.ECO_CLASS_OVER_BOOK_CAPACITY) as c2,
                     sum(T36562.BUS_CLASS_OVER_BOOK_CAPACITY) as c3,
                     T263.AIRPORT_CODE as c4,
                     T283.AIRPORT_CODE as c5,
                     T519.FLIGHT_NUMBER as c6,
                     T242.AIRCRAFT_REG_NUMBER as c7,
                     T242.AIRCRAFT_TYPE as c8,
                     T548.ROUTE as c9,
                     T386.FULL_DATE as c10,
                     case  when T283.AIRPORT_CODE = substr(T548.ROUTE , 1, 3) then 1 when T283.AIRPORT_CODE = substr(T548.ROUTE , 5 , 3) then 2 when T283.AIRPORT_CODE = substr(T548.ROUTE , 9 , 3) then 3 when T283.AIRPORT_CODE = substr(T548.ROUTE , 13 , 3) then 4 end  as c11,
                     T386.DATE_SID as c12,
                     ROW_NUMBER() OVER (PARTITION BY T242.AIRCRAFT_REG_NUMBER, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T519.FLIGHT_NUMBER, T548.ROUTE ORDER BY T242.AIRCRAFT_REG_NUMBER ASC, T263.AIRPORT_CODE ASC, T283.AIRPORT_CODE ASC, T386.DATE_SID ASC, T519.FLIGHT_NUMBER ASC, T548.ROUTE ASC) as c13
                from
                     DIM_ROUTE_MV T548 /* Dim Route */ ,
                     DIM_AIRCRAFT T242 /* Dim Aircraft */ ,
                     DIM_AIRPORT T283 /* Dim Departure Airport */ ,
                     DIM_AIRPORT T263 /* Dim Arrival Airport */ ,
                     DIM_DATE T386 /* Dim Scheduled Departure Date */ ,
                     DIM_FLIGHT T519 /* Dim Flight */ ,
                     DIM_SCHEDULE T36562 /* Fact Schedule */ ,
                     DIM_FLOWN_FLIGHT_BG T55924 /* Dim Flown Flight Bridge */
                where  ( T242.AIRCRAFT_SID = T55924.AIRCRAFT_SID and T263.AIRPORT_SID = T36562.LEG_ARRIVAL_AIRPORT_SID and T263.AI_OPERATED_AIRPORT_FLAG = 'Y' and T283.AIRPORT_SID = T36562.LEG_DEPARTURE_AIRPORT_SID and T283.AI_OPERATED_AIRPORT_FLAG = 'Y' and T386.DATE_SID = T36562.SCHEDULE_DEP_DATE_SID and T386.DATE_SID = T55924.SCHEDULE_DEP_DATE_SID and T519.FLIGHT_PREFIX = 'AI' and T519.FLIGHT_SID = T36562.FLIGHT_SID and T548.ROUTE_SID = T36562.ROUTE_SID and T36562.FLIGHT_SID = T55924.FLIGHT_SID and T36562.LEG_ARRIVAL_AIRPORT_SID = T55924.ARRIVAL_AIRPORT_SID and T36562.LEG_DEPARTURE_AIRPORT_SID = T55924.DEPARTURE_AIRPORT_SID and T263.AIRPORT_SID <> -1 and T283.AIRPORT_SID <> -1 and T386.DATE_SID <> -1 and T519.FLIGHT_SID <> -1 and T548.ROUTE_SID <> -1 and T36562.LEG_ARRIVAL_AIRPORT_SID <> T36562.LEG_DEPARTURE_AIRPORT_SID and instr(T548.ROUTE , concat(concat(T283.AIRPORT_CODE, '-'), T263.AIRPORT_CODE)) <> 0 and T386.FULL_DATE between TO_DATE('2013-01-18' , 'YYYY-MM-DD') and TO_DATE('2013-02-18' , 'YYYY-MM-DD') )
                group by T242.AIRCRAFT_REG_NUMBER, T242.AIRCRAFT_TYPE, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T386.FULL_DATE, T519.FLIGHT_NUMBER, T548.ROUTE
           ) D1
      where  ( D1.c13 = 1 ) ),
      SAWITH3 AS (select D1.c1 as c1,
           D1.c2 as c2,
           D1.c3 as c3,
           D1.c4 as c4,
           D1.c5 as c5,
           D1.c6 as c6,
           D1.c7 as c7,
           D1.c8 as c8,
           D1.c9 as c9,
           D1.c10 as c10,
           D1.c11 as c11,
           D1.c12 as c12,
           D1.c13 as c13,
           D1.c14 as c14
      from
           (select sum(case  when T6214.RBD_REV_FLAG = 'Y' and T51886.SRC_SYS_CODE = 'RA' and T6214.CLASS_CODE = 'Y' and T51886.COUPON_STATUS = 'F' then T51886.COUPON_PRORATE_AMT else NULL end ) as c1,
                     sum(case  when T6214.RBD_REV_FLAG = 'Y' and T51886.SRC_SYS_CODE = 'RA' and T6214.CLASS_CODE = 'C' and T51886.COUPON_STATUS = 'F' then T51886.COUPON_PRORATE_AMT else NULL end ) as c2,
                     sum(case  when T6214.RBD_REV_FLAG = 'Y' and T51886.SRC_SYS_CODE = 'RA' and T6214.CLASS_CODE = 'F' and T51886.COUPON_STATUS = 'F' then T51886.COUPON_PRORATE_AMT else NULL end ) as c3,
                     sum(case  when T6214.RBD_REV_FLAG <> 'Y' and T51886.COUPON_STATUS = 'F' and T51886.SRC_SYS_CODE = 'RA' then T51886.COUPON_PRORATE_AMT else NULL end ) as c4,
                     sum(T51886.COUPON_PRORATE_FUEL_SURCHARGE) as c5,
                     T263.AIRPORT_CODE as c6,
                     T283.AIRPORT_CODE as c7,
                     T519.FLIGHT_NUMBER as c8,
                     T242.AIRCRAFT_REG_NUMBER as c9,
                     T242.AIRCRAFT_TYPE as c10,
                     T548.ROUTE as c11,
                     T386.FULL_DATE as c12,
                     case  when T283.AIRPORT_CODE = substr(T548.ROUTE , 1, 3) then 1 when T283.AIRPORT_CODE = substr(T548.ROUTE , 5 , 3) then 2 when T283.AIRPORT_CODE = substr(T548.ROUTE , 9 , 3) then 3 when T283.AIRPORT_CODE = substr(T548.ROUTE , 13 , 3) then 4 end  as c13,
                     T386.DATE_SID as c14,
                     ROW_NUMBER() OVER (PARTITION BY T242.AIRCRAFT_REG_NUMBER, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T519.FLIGHT_NUMBER, T548.ROUTE ORDER BY T242.AIRCRAFT_REG_NUMBER ASC, T263.AIRPORT_CODE ASC, T283.AIRPORT_CODE ASC, T386.DATE_SID ASC, T519.FLIGHT_NUMBER ASC, T548.ROUTE ASC) as c15
                from
                     DIM_AIRCRAFT T242 /* Dim Aircraft */ ,
                     DIM_DATE T386 /* Dim Scheduled Departure Date */ ,
                     DIM_AIRPORT T263 /* Dim Arrival Airport */ ,
                     DIM_AIRPORT T283 /* Dim Departure Airport */ ,
                     DIM_FLIGHT T519 /* Dim Flight */ ,
                     DIM_ROUTE_MV T548 /* Dim Route */ ,
                     DIM_CLASS T6214 /* Dim Class */ ,
                     FACT_COUPON T51886 /* Fact Coupon */ ,
                     DIM_FLOWN_FLIGHT_BG T55924 /* Dim Flown Flight Bridge */
                where  ( T242.AIRCRAFT_SID = T55924.AIRCRAFT_SID and T263.AIRPORT_SID = T51886.ARRIVAL_AIRPORT_SID and T263.AI_OPERATED_AIRPORT_FLAG = 'Y' and T283.AIRPORT_SID = T51886.DEPARTURE_AIRPORT_SID and T283.AI_OPERATED_AIRPORT_FLAG = 'Y' and T386.DATE_SID = T51886.DEPARTURE_DATE_SID and T386.DATE_SID = T55924.SCHEDULE_DEP_DATE_SID and T519.FLIGHT_PREFIX = 'AI' and T519.FLIGHT_SID = T51886.FLIGHT_SID and T548.ROUTE_SID = T51886.ROUTE_SID and T6214.RBD_CLASS_SID = T51886.RBD_CLASS_SID and T51886.ARRIVAL_AIRPORT_SID = T55924.ARRIVAL_AIRPORT_SID and T51886.DEPARTURE_AIRPORT_SID = T55924.DEPARTURE_AIRPORT_SID and T51886.FLIGHT_SID = T55924.FLIGHT_SID and T263.AIRPORT_SID <> -1 and T283.AIRPORT_SID <> -1 and T386.DATE_SID <> -1 and T519.FLIGHT_SID <> -1 and T548.ROUTE_SID <> -1 and T51886.ARRIVAL_AIRPORT_SID <> T51886.DEPARTURE_AIRPORT_SID and instr(T548.ROUTE , concat(concat(T283.AIRPORT_CODE, '-'), T263.AIRPORT_CODE)) <> 0 and T386.FULL_DATE between TO_DATE('2013-01-18' , 'YYYY-MM-DD') and TO_DATE('2013-02-18' , 'YYYY-MM-DD') )
                group by T242.AIRCRAFT_REG_NUMBER, T242.AIRCRAFT_TYPE, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T386.FULL_DATE, T519.FLIGHT_NUMBER, T548.ROUTE
           ) D1
      where  ( D1.c15 = 1 ) ),
      SAWITH4 AS (select D1.c1 as c1,
           D1.c2 as c2,
           D1.c3 as c3,
           D1.c4 as c4,
           D1.c5 as c5,
           D1.c6 as c6,
           D1.c7 as c7,
           D1.c8 as c8,
           D1.c9 as c9,
           D1.c10 as c10,
           D1.c11 as c11,
           D1.c12 as c12,
           D1.c13 as c13,
           D1.c14 as c14,
           D1.c15 as c15,
           D1.c16 as c16,
           D1.c17 as c17,
           D1.c18 as c18,
           D1.c19 as c19,
           D1.c20 as c20,
           D1.c21 as c21,
           D1.c22 as c22,
           D1.c23 as c23,
           D1.c24 as c24,
           D1.c25 as c25,
           D1.c26 as c26,
           D1.c27 as c27,
           D1.c28 as c28,
           D1.c29 as c29,
           D1.c30 as c30
      from
           (select sum(T6403.EXCESS_BAGGAGE_WEIGHT * T6403.FLOWN_DISTANCE / 1000000) as c1,
                     sum(T6403.TOTAL_MAIL_WEIGHT * T6403.FLOWN_DISTANCE / 1000000) as c2,
                     sum(T6403.TOTAL_CARGO_WEIGHT * T6403.FLOWN_DISTANCE / 1000000) as c3,
                     sum(T6403.TONNES_KM_PERFORMED / 1000) as c4,
                     sum(T6403.TOTAL_CARGO_WEIGHT) as c5,
                     sum(T6403.RPKM_LEG_LEVEL) as c6,
                     sum(T6403.AVAILABLE_PAYLOAD) as c7,
                     sum(T99283.ASKM_LEG_LEVEL) as c8,
                     sum(T6403.AIRCRAFT_PAYLOAD) as c9,
                     sum(T6403.TOTAL_TONNES_KM_AVAILABLE / 1000000) as c10,
                     sum(T6403.TOTAL_MAIL_WEIGHT) as c11,
                     sum((nvl(T6403.TOTAL_PAX_WEIGHT , 0) + nvl(T6403.TOTAL_BAGGAGE_WEIGHT_LEG , 0)) * T6403.FLOWN_DISTANCE / 1000000) as c12,
                     sum(T6403.FLOWN_DISTANCE * T6403.TOTAL_NON_REV_PAX_BOARDED / 1000) as c13,
                     sum(T6403.FLOWN_DISTANCE * T6403.TOTAL_MAIL_WEIGHT_LEG / 1000000) as c14,
                     sum(T6403.FLOWN_DISTANCE) as c15,
                     sum(case  when T99283.LATEST_SOURCE_FLAG = 'CCF' then T99283.FIRST_CLASS_FLOWN_CAPACITY else T99283.SCH_FIRST_CLASS_BOOK_CAPACITY end ) as c16,
                     sum(T6403.EXCESS_BAGGAGE_WEIGHT) as c17,
                     sum(T6403.FLOWN_DISTANCE * T6403.EXCESS_BAGGAGE_WEIGHT_LEG / 1000000) as c18,
                     sum(case  when T99283.LATEST_SOURCE_FLAG = 'CCF' then T99283.ECO_CLASS_FLOWN_CAPACITY else T99283.SCH_ECO_CLASS_BOOK_CAPACITY end ) as c19,
                     sum(T6403.FLOWN_DISTANCE * T6403.TOTAL_CARGO_WEIGHT_LEG / 1000000) as c20,
                     sum(case  when T99283.LATEST_SOURCE_FLAG = 'CCF' then T99283.BUS_CLASS_FLOWN_CAPACITY else T99283.SCH_BUS_CLASS_BOOK_CAPACITY end ) as c21,
                     T263.AIRPORT_CODE as c22,
                     T283.AIRPORT_CODE as c23,
                     T519.FLIGHT_NUMBER as c24,
                     T242.AIRCRAFT_REG_NUMBER as c25,
                     T242.AIRCRAFT_TYPE as c26,
                     T548.ROUTE as c27,
                     T386.FULL_DATE as c28,
                     case  when T283.AIRPORT_CODE = substr(T548.ROUTE , 1, 3) then 1 when T283.AIRPORT_CODE = substr(T548.ROUTE , 5 , 3) then 2 when T283.AIRPORT_CODE = substr(T548.ROUTE , 9 , 3) then 3 when T283.AIRPORT_CODE = substr(T548.ROUTE , 13 , 3) then 4 end  as c29,
                     T386.DATE_SID as c30,
                     ROW_NUMBER() OVER (PARTITION BY T242.AIRCRAFT_REG_NUMBER, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T519.FLIGHT_NUMBER, T548.ROUTE ORDER BY T242.AIRCRAFT_REG_NUMBER ASC, T263.AIRPORT_CODE ASC, T283.AIRPORT_CODE ASC, T386.DATE_SID ASC, T519.FLIGHT_NUMBER ASC, T548.ROUTE ASC) as c31
                from
                     DIM_DATE T386 /* Dim Scheduled Departure Date */ ,
                     DIM_AIRCRAFT T242 /* Dim Aircraft */ ,
                     DIM_AIRPORT T263 /* Dim Arrival Airport */ ,
                     DIM_AIRPORT T283 /* Dim Departure Airport */ ,
                     (
                          (
                               DIM_ROUTE_MV T548 /* Dim Route */  left outer join (
                                    DIM_FLIGHT T519 /* Dim Flight */  left outer join FACT_DCS T6403 /* Fact Departure Control System */  On T519.FLIGHT_SID = T6403.FLIGHT_SID) On T548.ROUTE_SID = T6403.ROUTE_SID) left outer join (SELECT
      FACT_BIR_FLIGHT.FLIGHT_SID FLIGHT_SID,
      FACT_BIR_FLIGHT.FLIGHT_DATE FLIGHT_DATE,
      BAG.DB_STATION_OR_CITY_CODE DB_STATION_OR_CITY_CODE,
      ARR.AIRPORT_SID AIRPORT_SID,
      max(case  when ELEMENTS.ELEMENT_XML_TAG = 'CS' and ELEMENTS.ELEMENT_ATTRIBUTE = 'USD_EQUIVALENT' then
      ELEMENTS.ELEMENT_VALUE end) COMPENSATION,
      sum(BAG.MB_NUMBER_OF_BAGS) MB_NUMBER_OF_BAGS

      FROM
      DIM_BIR_FILE,
      DIM_AIRPORT ARR,
      FACT_BIR_FLIGHT,
      FACT_BIR_BAG BAG,
      FACT_BIR_ELEMENTS_M ELEMENTS
      WHERE
      DIM_BIR_FILE.WTR_ID = FACT_BIR_FLIGHT.WTR_ID
      AND DIM_BIR_FILE.WTR_ID = BAG.WTR_ID
      AND DIM_BIR_FILE.WTR_ID = ELEMENTS.WTR_ID
      AND ARR.AIRPORT_CODE = BAG.DB_STATION_OR_CITY_CODE
      AND ARR.AI_OPERATED_AIRPORT_FLAG='Y'
      AND ARR.AIRPORT_SID <> -1
      AND ELEMENTS.ELEMENT_XML_TAG = 'CS'
      AND ELEMENTS.ELEMENT_ATTRIBUTE = 'USD_EQUIVALENT'

      group by
      FACT_BIR_FLIGHT.FLIGHT_SID ,
      FACT_BIR_FLIGHT.FLIGHT_DATE ,
      BAG.DB_STATION_OR_CITY_CODE ,
      ARR.AIRPORT_SID) T107993 On T6403.FLIGHT_SID = T107993.FLIGHT_SID and T6403.ARRIVAL_AIRPORT_SID = T107993.AIRPORT_SID and substr(cast(T6403.SCH_DEPARTURE_TIME as  CHARACTER ( 30 ) ) , 0 , 9) = substr(cast(T107993.FLIGHT_DATE as  CHARACTER ( 30 ) ) , 0 , 9)) left outer join DIM_FLIGHT_CAPACITY T99283 /* Dim Flight Capacity */  On T6403.ARRIVAL_AIRPORT_SID = T99283.ARRIVAL_AIRPORT_SID and T6403.DEPARTURE_AIRPORT_SID = T99283.DEPARTURE_AIRPORT_SID and T6403.FLIGHT_SID = T99283.FLIGHT_SID and T6403.DEPARTURE_DATE_SID = T99283.DATE_SID
                where  ( T242.AIRCRAFT_SID = T6403.AIRCRAFT_SID and T263.AIRPORT_SID = T6403.ARRIVAL_AIRPORT_SID and T263.AI_OPERATED_AIRPORT_FLAG = 'Y' and T283.AIRPORT_SID = T6403.DEPARTURE_AIRPORT_SID and T283.AI_OPERATED_AIRPORT_FLAG = 'Y' and T386.DATE_SID = T6403.DEPARTURE_DATE_SID and T519.FLIGHT_PREFIX = 'AI' and T263.AIRPORT_SID <> -1 and T283.AIRPORT_SID <> -1 and T386.DATE_SID <> -1 and T519.FLIGHT_SID <> -1 and T548.ROUTE_SID <> -1 and T6403.DEPARTURE_AIRPORT_SID <> T6403.ARRIVAL_AIRPORT_SID and instr(T548.ROUTE , concat(concat(T283.AIRPORT_CODE, '-'), T263.AIRPORT_CODE)) <> 0 and T386.FULL_DATE between TO_DATE('2013-01-18' , 'YYYY-MM-DD') and TO_DATE('2013-02-18' , 'YYYY-MM-DD') )
                group by T242.AIRCRAFT_REG_NUMBER, T242.AIRCRAFT_TYPE, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T386.FULL_DATE, T519.FLIGHT_NUMBER, T548.ROUTE
           ) D1
      where  ( D1.c31 = 1 ) ),
      SAWITH5 AS (select D1.c1 as c1,
           D1.c2 as c2,
           D1.c3 as c3,
           D1.c4 as c4,
           D1.c5 as c5,
           D1.c6 as c6,
           D1.c7 as c7,
           D1.c8 as c8,
           D1.c9 as c9,
           D1.c10 as c10,
           D1.c11 as c11,
           D1.c12 as c12,
           D1.c13 as c13
      from
           (select sum(T91680.TOTAL_CARGO_REVENUE) as c1,
                     sum(T91680.EXCESS_BAGGAGE_REVENUE) as c2,
                     sum(T91680.MAIL_REVENUE) as c3,
                     sum(T536.LEG_DISTANCE * T91680.NON_REV_CARGO_WEIGHT_CARRIED / 1000000) as c4,
                     T263.AIRPORT_CODE as c5,
                     T283.AIRPORT_CODE as c6,
                     T519.FLIGHT_NUMBER as c7,
                     T242.AIRCRAFT_REG_NUMBER as c8,
                     T242.AIRCRAFT_TYPE as c9,
                     T548.ROUTE as c10,
                     T386.FULL_DATE as c11,
                     case  when T283.AIRPORT_CODE = substr(T548.ROUTE , 1, 3) then 1 when T283.AIRPORT_CODE = substr(T548.ROUTE , 5 , 3) then 2 when T283.AIRPORT_CODE = substr(T548.ROUTE , 9 , 3) then 3 when T283.AIRPORT_CODE = substr(T548.ROUTE , 13 , 3) then 4 end  as c12,
                     T386.DATE_SID as c13,
                     ROW_NUMBER() OVER (PARTITION BY T242.AIRCRAFT_REG_NUMBER, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T519.FLIGHT_NUMBER, T548.ROUTE ORDER BY T242.AIRCRAFT_REG_NUMBER ASC, T263.AIRPORT_CODE ASC, T283.AIRPORT_CODE ASC, T386.DATE_SID ASC, T519.FLIGHT_NUMBER ASC, T548.ROUTE ASC) as c14
                from
                     DIM_ROUTE_MV T548 /* Dim Route */ ,
                     DIM_AIRCRAFT T242 /* Dim Aircraft */ ,
                     DIM_FLIGHT T519 /* Dim Flight */ ,
                     DIM_DATE T386 /* Dim Scheduled Departure Date */ ,
                     DIM_AIRPORT T263 /* Dim Arrival Airport */ ,
                     DIM_AIRPORT T283 /* Dim Departure Airport */ ,
                     DIM_LEG T536 /* Dim Leg */ ,
                     FACT_CARGO T91680 /* Fact Cargo */
                where  ( T242.AIRCRAFT_SID = T91680.AIRCRAFT_SID and T263.AIRPORT_SID = T91680.ARRIVAL_AIRPORT_SID and T263.AI_OPERATED_AIRPORT_FLAG = 'Y' and T283.AIRPORT_SID = T91680.DEPARTURE_AIRPORT_SID and T283.AI_OPERATED_AIRPORT_FLAG = 'Y' and T386.DATE_SID = T91680.DEPARTURE_DATE_SID and T519.FLIGHT_PREFIX = 'AI' and T519.FLIGHT_SID = T91680.FLIGHT_SID and T536.LEG_SID = T91680.LEG_SID and T548.ROUTE_SID = T91680.ROUTE_SID and T263.AIRPORT_SID <> -1 and T283.AIRPORT_SID <> -1 and T386.DATE_SID <> -1 and T519.FLIGHT_SID <> -1 and T548.ROUTE_SID <> -1 and T91680.ARRIVAL_AIRPORT_SID <> T91680.DEPARTURE_AIRPORT_SID and instr(T548.ROUTE , concat(concat(T283.AIRPORT_CODE, '-'), T263.AIRPORT_CODE)) <> 0 and T386.FULL_DATE between TO_DATE('2013-01-18' , 'YYYY-MM-DD') and TO_DATE('2013-02-18' , 'YYYY-MM-DD') )
                group by T242.AIRCRAFT_REG_NUMBER, T242.AIRCRAFT_TYPE, T263.AIRPORT_CODE, T283.AIRPORT_CODE, T386.DATE_SID, T386.FULL_DATE, T519.FLIGHT_NUMBER, T548.ROUTE
           ) D1
      where  ( D1.c14 = 1 ) ),
      SAWITH6 AS (select D1.c1 as c1,
           D1.c2 as c2,
           D1.c3 as c3,
           D1.c4 as c4,
           D1.c5 as c5,
           D1.c6 as c6,
           D1.c7 as c7,
           D1.c8 as c8,
           D1.c9 as c9,
           D1.c10 as c10,
           D1.c11 as c11,
           D1.c12 as c12,
           D1.c13 as c13,
           D1.c14 as c14,
           D1.c15 as c15,
           D1.c16 as c16,
           D1.c17 as c17,
           D1.c18 as c18,
           D1.c19 as c19,
           D1.c20 as c20,
           D1.c21 as c21,
           D1.c22 as c22,
           D1.c23 as c23,
           D1.c24 as c24,
           D1.c25 as c25,
           D1.c26 as c26,
           D1.c27 as c27,
           D1.c28 as c28,
           D1.c29 as c29,
           D1.c30 as c30,
           D1.c31 as c31,
           D1.c32 as c32,
           D1.c33 as c33,
           D1.c34 as c34,
           D1.c35 as c35,
           D1.c36 as c36,
           D1.c37 as c37,
           D1.c38 as c38,
           D1.c39 as c39,
           D1.c40 as c40,
           D1.c41 as c41,
           D1.c42 as c42,
           D1.c43 as c43,
           D1.c44 as c44,
           D1.c45 as c45,
           D1.c46 as c46,
           D1.c47 as c47,
           D1.c48 as c48,
           D1.c49 as c49,
           D1.c50 as c50,
           D1.c51 as c51,
           D1.c52 as c52,
           D1.c53 as c53,
           D1.c54 as c54,
           D1.c55 as c55,
           D1.c102 as c102,
           D1.c103 as c103,
           D1.c104 as c104,
           D1.c105 as c105,
           D1.c106 as c106,
           D1.c107 as c107,
           D1.c108 as c108,
           D1.c109 as c109,
           D1.c110 as c110,
           D1.c111 as c111,
           D1.c112 as c112
      from
           (select 0 as c1,
                     case  when D1.c8 is not null then D1.c8 when D2.c3 is not null then D2.c3 when D3.c4 is not null then D3.c4 when D4.c6 is not null then D4.c6 when D5.c22 is not null then D5.c22 when D6.c5 is not null then D6.c5 end  as c2,
                     case  when D1.c9 is not null then D1.c9 when D2.c4 is not null then D2.c4 when D3.c5 is not null then D3.c5 when D4.c7 is not null then D4.c7 when D5.c23 is not null then D5.c23 when D6.c6 is not null then D6.c6 end  as c3,
                     case  when D1.c10 is not null then D1.c10 when D2.c5 is not null then D2.c5 when D3.c6 is not null then D3.c6 when D4.c8 is not null then D4.c8 when D5.c24 is not null then D5.c24 when D6.c7 is not null then D6.c7 end  as c4,
                     case  when D1.c11 is not null then D1.c11 when D2.c6 is not null then D2.c6 when D3.c7 is not null then D3.c7 when D4.c9 is not null then D4.c9 when D5.c25 is not null then D5.c25 when D6.c8 is not null then D6.c8 end  as c5,
                     case  when D1.c12 is not null then D1.c12 when D2.c7 is not null then D2.c7 when D3.c8 is not null then D3.c8 when D4.c10 is not null then D4.c10 when D5.c26 is not null then D5.c26 when D6.c9 is not null then D6.c9 end  as c6,
                     case  when D1.c13 is not null then D1.c13 when D2.c8 is not null then D2.c8 when D3.c9 is not null then D3.c9 when D4.c11 is not null then D4.c11 when D5.c27 is not null then D5.c27 when D6.c10 is not null then D6.c10 end  as c7,
                     case  when D1.c14 is not null then D1.c14 when D2.c9 is not null then D2.c9 when D3.c10 is not null then D3.c10 when D4.c12 is not null then D4.c12 when D5.c28 is not null then D5.c28 when D6.c11 is not null then D6.c11 end  as c8,
                     case  when D1.c15 is not null then D1.c15 when D2.c10 is not null then D2.c10 when D3.c11 is not null then D3.c11 when D4.c13 is not null then D4.c13 when D5.c29 is not null then D5.c29 when D6.c12 is not null then D6.c12 end  as c9,
                     D6.c2 as c10,
                     D6.c4 as c11,
                     D6.c3 as c12,
                     D5.c9 / 1000 as c13,
                     D5.c8 / 1000 as c14,
                     D5.c7 / 1000 as c15,
                     D5.c21 as c16,
                     D1.c6 as c17,
                     D1.c3 as c18,
                     D5.c20 as c19,
                     D5.c19 as c20,
                     D1.c5 as c21,
                     D1.c2 as c22,
                     D5.c18 as c23,
                     D5.c17 as c24,
                     D5.c16 as c25,
                     D1.c7 as c26,
                     D1.c7 + D1.c6 + D1.c5 as c27,
                     D1.c4 as c28,
                     D1.c4 + D1.c3 + D1.c2 as c29,
                     D5.c15 as c30,
                     D1.c1 as c31,
                     D5.c14 as c32,
                     D5.c13 as c33,
                     D5.c12 as c34,
                     D5.c6 / nullif( D5.c8, 0) * 100 as c35,
                     D5.c6 / 1000 as c36,
                     D5.c5 / 1000 as c37,
                     D5.c11 as c38,
                     D5.c10 as c39,
                     D2.c2 as c40,
                     D2.c1 as c41,
                     D3.c3 as c42,
                     D3.c2 as c43,
                     D3.c1 as c44,
                     D4.c2 as c45,
                     D4.c5 as c46,
                     D4.c1 as c47,
                     D4.c3 as c48,
                     D4.c3 + D4.c2 + D4.c1 + D4.c4 as c49,
                     D4.c4 as c50,
                     (case  when not D5.c4 is null or not D5.c3 is null or not D5.c2 is null or not D5.c1 is null then cast(nvl(D5.c4 , 0) + nvl(D5.c3 , 0) + nvl(D5.c2 , 0) + nvl(D5.c1 , 0) as  DOUBLE PRECISION  ) end  / nullif( cast(D5.c10 as  DOUBLE PRECISION  ), 0)) / 10 as c51,
                     case  when not D5.c4 is null or not D5.c3 is null or not D5.c2 is null or not D5.c1 is null then nvl(D5.c4 , 0) + nvl(D5.c3 , 0) + nvl(D5.c2 , 0) + nvl(D5.c1 , 0) end  / 1000 as c52,
                     nvl(D6.c1 , 0) as c53,
                     nvl(D4.c3 , 0) + nvl(D4.c2 , 0) + nvl(D4.c1 , 0) + nvl(D4.c4 , 0) + nvl(D6.c1 , 0) + nvl(D6.c3 , 0) + nvl(D6.c2 , 0) as c54,
                     nvl(D4.c3 , 0) + nvl(D4.c2 , 0) + nvl(D4.c1 , 0) as c55,
                     case  when D1.c16 is not null then D1.c16 when D2.c11 is not null then D2.c11 when D3.c12 is not null then D3.c12 when D4.c14 is not null then D4.c14 when D5.c30 is not null then D5.c30 when D6.c13 is not null then D6.c13 end  as c102,
                     D5.c9 as c103,
                     D5.c8 as c104,
                     D5.c7 as c105,
                     D5.c6 as c106,
                     D5.c5 as c107,
                     D5.c4 as c108,
                     D5.c3 as c109,
                     D5.c2 as c110,
                     D5.c1 as c111,
                     D6.c1 as c112,
                     ROW_NUMBER() OVER (PARTITION BY case  when D1.c8 is not null then D1.c8 when D2.c3 is not null then D2.c3 when D3.c4 is not null then D3.c4 when D4.c6 is not null then D4.c6 when D5.c22 is not null then D5.c22 when D6.c5 is not null then D6.c5 end , case  when D1.c9 is not null then D1.c9 when D2.c4 is not null then D2.c4 when D3.c5 is not null then D3.c5 when D4.c7 is not null then D4.c7 when D5.c23 is not null then D5.c23 when D6.c6 is not null then D6.c6 end , case  when D1.c10 is not null then D1.c10 when D2.c5 is not null then D2.c5 when D3.c6 is not null then D3.c6 when D4.c8 is not null then D4.c8 when D5.c24 is not null then D5.c24 when D6.c7 is not null then D6.c7 end , case  when D1.c11 is not null then D1.c11 when D2.c6 is not null then D2.c6 when D3.c7 is not null then D3.c7 when D4.c9 is not null then D4.c9 when D5.c25 is not null then D5.c25 when D6.c8 is not null then D6.c8 end , case  when D1.c12 is not null then D1.c12 when D2.c7 is not null then D2.c7 when D3.c8 is not null then D3.c8 when D4.c10 is not null then D4.c10 when D5.c26 is not null then D5.c26 when D6.c9 is not null then D6.c9 end , case  when D1.c13 is not null then D1.c13 when D2.c8 is not null then D2.c8 when D3.c9 is not null then D3.c9 when D4.c11 is not null then D4.c11 when D5.c27 is not null then D5.c27 when D6.c10 is not null then D6.c10 end , case  when D1.c14 is not null then D1.c14 when D2.c9 is not null then D2.c9 when D3.c10 is not null then D3.c10 when D4.c12 is not null then D4.c12 when D5.c28 is not null then D5.c28 when D6.c11 is not null then D6.c11 end , case  when D1.c16 is not null then D1.c16 when D2.c11 is not null then D2.c11 when D3.c12 is not null then D3.c12 when D4.c14 is not null then D4.c14 when D5.c30 is not null then D5.c30 when D6.c13 is not null then D6.c13 end  ORDER BY case  when D1.c8 is not null then D1.c8 when D2.c3 is not null then D2.c3 when D3.c4 is not null then D3.c4 when D4.c6 is not null then D4.c6 when D5.c22 is not null then D5.c22 when D6.c5 is not null then D6.c5 end  ASC, case  when D1.c9 is not null then D1.c9 when D2.c4 is not null then D2.c4 when D3.c5 is not null then D3.c5 when D4.c7 is not null then D4.c7 when D5.c23 is not null then D5.c23 when D6.c6 is not null then D6.c6 end  ASC, case  when D1.c10 is not null then D1.c10 when D2.c5 is not null then D2.c5 when D3.c6 is not null then D3.c6 when D4.c8 is not null then D4.c8 when D5.c24 is not null then D5.c24 when D6.c7 is not null then D6.c7 end  ASC, case  when D1.c11 is not null then D1.c11 when D2.c6 is not null then D2.c6 when D3.c7 is not null then D3.c7 when D4.c9 is not null then D4.c9 when D5.c25 is not null then D5.c25 when D6.c8 is not null then D6.c8 end  ASC, case  when D1.c12 is not null then D1.c12 when D2.c7 is not null then D2.c7 when D3.c8 is not null then D3.c8 when D4.c10 is not null then D4.c10 when D5.c26 is not null then D5.c26 when D6.c9 is not null then D6.c9 end  ASC, case  when D1.c13 is not null then D1.c13 when D2.c8 is not null then D2.c8 when D3.c9 is not null then D3.c9 when D4.c11 is not null then D4.c11 when D5.c27 is not null then D5.c27 when D6.c10 is not null then D6.c10 end  ASC, case  when D1.c14 is not null then D1.c14 when D2.c9 is not null then D2.c9 when D3.c10 is not null then D3.c10 when D4.c12 is not null then D4.c12 when D5.c28 is not null then D5.c28 when D6.c11 is not null then D6.c11 end  ASC, case  when D1.c16 is not null then D1.c16 when D2.c11 is not null then D2.c11 when D3.c12 is not null then D3.c12 when D4.c14 is not null then D4.c14 when D5.c30 is not null then D5.c30 when D6.c13 is not null then D6.c13 end  ASC) as c113
                from
                     (
                          (
                               (
                                    (
                                         SAWITH0 D1 full outer join SAWITH1 D2 On D1.c13 = D2.c8 and D1.c11 = D2.c6 and D1.c10 = D2.c5 and D1.c9 = D2.c4 and D1.c8 = D2.c3 and D1.c16 = D2.c11) full outer join SAWITH2 D3 On D3.c7 = case  when D1.c11 is not null then D1.c11 when D2.c6 is not null then D2.c6 end  and D3.c6 = case  when D1.c10 is not null then D1.c10 when D2.c5 is not null then D2.c5 end  and D3.c5 = case  when D1.c9 is not null then D1.c9 when D2.c4 is not null then D2.c4 end  and D3.c4 = case  when D1.c8 is not null then D1.c8 when D2.c3 is not null then D2.c3 end  and D3.c9 = case  when D1.c13 is not null then D1.c13 when D2.c8 is not null then D2.c8 end  and D3.c12 = case  when D1.c16 is not null then D1.c16 when D2.c11 is not null then D2.c11 end ) full outer join SAWITH3 D4 On D4.c9 = case  when D1.c11 is not null then D1.c11 when D2.c6 is not null then D2.c6 when D3.c7 is not null then D3.c7 end  and D4.c8 = case  when D1.c10 is not null then D1.c10 when D2.c5 is not null then D2.c5 when D3.c6 is not null then D3.c6 end  and D4.c7 = case  when D1.c9 is not null then D1.c9 when D2.c4 is not null then D2.c4 when D3.c5 is not null then D3.c5 end  and D4.c6 = case  when D1.c8 is not null then D1.c8 when D2.c3 is not null then D2.c3 when D3.c4 is not null then D3.c4 end  and D4.c11 = case  when D1.c13 is not null then D1.c13 when D2.c8 is not null then D2.c8 when D3.c9 is not null then D3.c9 end  and D4.c14 = case  when D1.c16 is not null then D1.c16 when D2.c11 is not null then D2.c11 when D3.c12 is not null then D3.c12 end ) full outer join SAWITH4 D5 On D5.c25 = case  when D1.c11 is not null then D1.c11 when D2.c6 is not null then D2.c6 when D3.c7 is not null then D3.c7 when D4.c9 is not null then D4.c9 end  and D5.c24 = case  when D1.c10 is not null then D1.c10 when D2.c5 is not null then D2.c5 when D3.c6 is not null then D3.c6 when D4.c8 is not null then D4.c8 end  and D5.c23 = case  when D1.c9 is not null then D1.c9 when D2.c4 is not null then D2.c4 when D3.c5 is not null then D3.c5 when D4.c7 is not null then D4.c7 end  and D5.c22 = case  when D1.c8 is not null then D1.c8 when D2.c3 is not null then D2.c3 when D3.c4 is not null then D3.c4 when D4.c6 is not null then D4.c6 end  and D5.c27 = case  when D1.c13 is not null then D1.c13 when D2.c8 is not null then D2.c8 when D3.c9 is not null then D3.c9 when D4.c11 is not null then D4.c11 end  and D5.c30 = case  when D1.c16 is not null then D1.c16 when D2.c11 is not null then D2.c11 when D3.c12 is not null then D3.c12 when D4.c14 is not null then D4.c14 end ) full outer join SAWITH5 D6 On D6.c8 = case  when D1.c11 is not null then D1.c11 when D2.c6 is not null then D2.c6 when D3.c7 is not null then D3.c7 when D4.c9 is not null then D4.c9 when D5.c25 is not null then D5.c25 end  and D6.c7 = case  when D1.c10 is not null then D1.c10 when D2.c5 is not null then D2.c5 when D3.c6 is not null then D3.c6 when D4.c8 is not null then D4.c8 when D5.c24 is not null then D5.c24 end  and D6.c6 = case  when D1.c9 is not null then D1.c9 when D2.c4 is not null then D2.c4 when D3.c5 is not null then D3.c5 when D4.c7 is not null then D4.c7 when D5.c23 is not null then D5.c23 end  and D6.c5 = case  when D1.c8 is not null then D1.c8 when D2.c3 is not null then D2.c3 when D3.c4 is not null then D3.c4 when D4.c6 is not null then D4.c6 when D5.c22 is not null then D5.c22 end  and D6.c10 = case  when D1.c13 is not null then D1.c13 when D2.c8 is not null then D2.c8 when D3.c9 is not null then D3.c9 when D4.c11 is not null then D4.c11 when D5.c27 is not null then D5.c27 end  and D6.c13 = case  when D1.c16 is not null then D1.c16 when D2.c11 is not null then D2.c11 when D3.c12 is not null then D3.c12 when D4.c14 is not null then D4.c14 when D5.c30 is not null then D5.c30 end
           ) D1
      where  ( D1.c113 = 1 ) )
      select D1.c1 as c1,
           D1.c2 as c2,
           D1.c3 as c3,
           D1.c4 as c4,
           D1.c5 as c5,
           D1.c6 as c6,
           D1.c7 as c7,
           D1.c8 as c8,
           D1.c9 as c9,
           D1.c10 as c10,
           D1.c11 as c11,
           D1.c12 as c12,
           D1.c13 as c13,
           D1.c14 as c14,
           D1.c15 as c15,
           D1.c16 as c16,
           D1.c17 as c17,
           D1.c18 as c18,
           D1.c19 as c19,
           D1.c20 as c20,
           D1.c21 as c21,
           D1.c22 as c22,
           D1.c23 as c23,
           D1.c24 as c24,
           D1.c25 as c25,
           D1.c26 as c26,
           D1.c27 as c27,
           D1.c28 as c28,
           D1.c29 as c29,
           D1.c30 as c30,
           D1.c31 as c31,
           D1.c32 as c32,
           D1.c33 as c33,
           D1.c34 as c34,
           D1.c35 as c35,
           D1.c36 as c36,
           D1.c37 as c37,
           D1.c38 as c38,
           D1.c39 as c39,
           D1.c40 as c40,
           D1.c41 as c41,
           D1.c42 as c42,
           D1.c43 as c43,
           D1.c44 as c44,
           D1.c45 as c45,
           D1.c46 as c46,
           D1.c47 as c47,
           D1.c48 as c48,
           D1.c49 as c49,
           D1.c50 as c50,
           D1.c51 as c51,
           D1.c52 as c52,
           D1.c53 as c53,
           D1.c54 as c54,
           D1.c55 as c55,
           D1.c102 as c103,
           D1.c103 as c105,
           D1.c104 as c106,
           D1.c105 as c107,
           D1.c106 as c108,
           D1.c107 as c109,
           D1.c108 as c110,
           D1.c109 as c111,
           D1.c110 as c112,
           D1.c111 as c113,
           D1.c112 as c114
      from
           SAWITH6 D1
      order by c5, c6, c2, c3, c103, c8, c4, c7, c9

      ]]