1 Reply Latest reply: Aug 14, 2013 6:45 PM by user10615659 RSS

    11g SQL query syntax/results differ from 10g

    Mike Jelen

      Hello,

       

      A bit of an odd situation.  We have a report in 10g that is working as expected and when we run the same report in 11g we slightly different results.  The same physical tables and columns are being used between each report and the number of records (17) returned to OBI are the same.  Both queries are pulling from the same database with the same user id.  As there are differences in how 10g runs queries vs 11g, are there any odd behaviors that I should keep an eye out for in how 11g assembles the data that's returned from the db prior to presenting it on the Analysis?  

       

      10g Results (correct):

      Region...............Actuals (Prior Qtr).....Up...........Plan Amount....................% of Plan

      AP...........................10,489..............8,965..............................................................

      Americas................114,208...........110,779..................6...........................1969411% 

      EMEA.....................26,799..............23,976..............................................................

      UNASSIGNED....................................................149,957...........................0%

      Grand Total.............151,496...........143,721..........149,962.........................96%

       

       

      11g Results (incorrect):

      Region...............Actuals (Prior Qtr).....Up...........Plan Amount....................% of Plan

      AP...........................10,180..............8,965............................................................

      Americas.................90,878...........110,779............................................................ 

      EMEA.....................24,978.............23,976............................................................

      UNASSIGNED....................................................149,957...........................0%

      Grand Total.............126,037..........143,721..........149,957.........................96%

       

      Thank you!

      Mike

       

      -------------------- 10g query--------------------------

       

      -------------------- Sending query to database named EBS Rapid Data Store (id: <<5087545>>):
      WITH
      SAWITH0 AS (select T28761.PERIOD_NAME as c2,
           T28761.PERIOD_START_DATE as c3,
           T28761.QUARTER_RANK as c4,
           ROW_NUMBER() OVER (PARTITION BY T28761.QUARTER_RANK ORDER BY T28761.QUARTER_RANK DESC) as c5
      from
           XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */ ),
      SAWITH1 AS (select Case when case SAWITH0.c5 when 1 then SAWITH0.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH0.c5 when 1 then SAWITH0.c3 else NULL end  ASC NULLS LAST ) end as c1,
           SAWITH0.c2 as c2,
           SAWITH0.c4 as c3
      from
           SAWITH0),
      SAWITH2 AS (select min(SAWITH1.c1) over (partition by SAWITH1.c3)  as c1,
           SAWITH1.c2 as c2
      from
           SAWITH1),
      SAWITH3 AS (select distinct SAWITH2.c1 + 1 as c1,
           SAWITH2.c2 as c2
      from
           SAWITH2),
      SAWITH4 AS (select T28761.QUARTER_RANK as c2,
           T28761.QUARTER_YEAR_NAME as c3,
           T28761.PERIOD_START_DATE as c4,
           ROW_NUMBER() OVER (PARTITION BY T28761.QUARTER_RANK ORDER BY T28761.QUARTER_RANK DESC) as c5
      from
           XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */ ),
      SAWITH5 AS (select Case when case SAWITH4.c5 when 1 then SAWITH4.c4 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH4.c5 when 1 then SAWITH4.c4 else NULL end  ASC NULLS LAST ) end as c1,
           SAWITH4.c2 as c2,
           SAWITH4.c3 as c3
      from
           SAWITH4),
      SAWITH6 AS (select distinct min(SAWITH5.c1) over (partition by SAWITH5.c2)  as c1,
           SAWITH5.c2 as c2,
           SAWITH5.c3 as c3
      from
           SAWITH5),
      SAWITH7 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
      from
           (select sum(case  when T37838.POL_VERSION_FLAG = 'C' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 then nvl(T37838.AMOUNT , 0) when T37838.PERIOD_NAME <> 'March-13' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 and T37838.POL_VERSION_FLAG is null then nvl(T37838.AMOUNT , 0) else 0 end ) as c1,
                     SAWITH6.c3 as c2,
                     T41894.ICN_GROUP as c3,
                     SAWITH6.c2 as c4,
                     T41894.ICN_GROUP_CODE as c5,
                     T30728.PARENT_REGION as c6,
                     ROW_NUMBER() OVER (PARTITION BY T30728.PARENT_REGION, T41894.ICN_GROUP_CODE, SAWITH6.c2 ORDER BY T30728.PARENT_REGION ASC, T41894.ICN_GROUP_CODE ASC, SAWITH6.c2 ASC) as c7
                from
                     SAWITH3 left outer join (
                          (
                                    XXFI.XXFI_REVFCST_POL_REPORTING_V T37838 /* Fact_POL_Snapshot */  left outer join
                                    XXFI.XXFI_GEO_REGION_ACCUM T30728 /* Dim_Regions */  On T30728.COUNTRY_CODE = T37838.SHIP_TO_COUNTRY_CODE2) left outer join
                               XXFI.XXFI_ICN_OWNERS_V T41894 /* Dim_ICN_Override */  On T37838.OVERRIDE_ICN = T41894.ICN_CODE) On SAWITH3.c2 = T37838.PERIOD_NAME,
                     SAWITH6
                where  ( SAWITH6.c1 = SAWITH3.c1 and SAWITH6.c3 = '2010-Q2' and (T37838.PROSPECT_NUMBER is null or T37838.PROSPECT_NUMBER not like '%Budget%') )
                group by T30728.PARENT_REGION, T41894.ICN_GROUP_CODE, T41894.ICN_GROUP, SAWITH6.c2, SAWITH6.c3
           ) D1
      where  ( D1.c7 = 1 ) ),
      SAWITH8 AS (select sum(case  when T37838.POL_VERSION_FLAG = 'C' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 then nvl(T37838.AMOUNT , 0) when T37838.PERIOD_NAME <> 'March-13' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 and T37838.POL_VERSION_FLAG is null then nvl(T37838.AMOUNT , 0) else 0 end ) as c1,
           sum(case  when T37838.POL_VERSION_FLAG = 'C' and T37838.FORECAST_PROBABILITY_PERCENT >= 50 then nvl(T37838.AMOUNT , 0) when T37838.PERIOD_NAME <> 'March-13' and T37838.FORECAST_PROBABILITY_PERCENT >= 50 and T37838.POL_VERSION_FLAG is null then nvl(T37838.AMOUNT , 0) else 0 end ) as c2,
           T30728.PARENT_REGION as c3,
           T28761.QUARTER_YEAR_NAME as c6,
           T41894.ICN_GROUP as c7,
           T28761.QUARTER_RANK as c8,
           T41894.ICN_GROUP_CODE as c9
      from
           (
                (
                          XXFI.XXFI_REVFCST_POL_REPORTING_V T37838 /* Fact_POL_Snapshot */  left outer join
                          XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */  On T28761.PERIOD_NAME = T37838.PERIOD_NAME) left outer join
                     XXFI.XXFI_GEO_REGION_ACCUM T30728 /* Dim_Regions */  On T30728.COUNTRY_CODE = T37838.SHIP_TO_COUNTRY_CODE2) left outer join
                XXFI.XXFI_ICN_OWNERS_V T41894 /* Dim_ICN_Override */  On T37838.OVERRIDE_ICN = T41894.ICN_CODE
      where  ( T28761.QUARTER_YEAR_NAME = '2010-Q2' and (T37838.PROSPECT_NUMBER is null or T37838.PROSPECT_NUMBER not like '%Budget%') )
      group by T28761.QUARTER_YEAR_NAME, T28761.QUARTER_RANK, T30728.PARENT_REGION, T41894.ICN_GROUP_CODE, T41894.ICN_GROUP),
      SAWITH9 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
      from
           (select sum(SAWITH8.c1) over (partition by SAWITH8.c3)  as c1,
                     sum(SAWITH8.c2) over (partition by SAWITH8.c3)  as c2,
                     SAWITH8.c3 as c3,
                     sum(SAWITH8.c1) over (partition by SAWITH8.c8, SAWITH8.c9, SAWITH8.c3)  as c4,
                     sum(SAWITH8.c2) over (partition by SAWITH8.c8, SAWITH8.c9, SAWITH8.c3)  as c5,
                     SAWITH8.c6 as c6,
                     SAWITH8.c7 as c7,
                     SAWITH8.c8 as c8,
                     SAWITH8.c9 as c9,
                     ROW_NUMBER() OVER (PARTITION BY SAWITH8.c3, SAWITH8.c8, SAWITH8.c9 ORDER BY SAWITH8.c3 ASC, SAWITH8.c8 ASC, SAWITH8.c9 ASC) as c10
                from
                     SAWITH8
           ) D1
      where  ( D1.c10 = 1 ) ),
      SAWITH10 AS (select sum(T34877.AMOUNT) as c1,
           T28761.QUARTER_YEAR_NAME as c3,
           T30728.PARENT_REGION as c4,
           T41894.ICN_GROUP as c5,
           T28761.QUARTER_RANK as c6,
           T41894.ICN_GROUP_CODE as c7
      from
           (
                (
                          XXFI.XXFI_REVENUE_BUDGET_ACCUM T34877 /* Fact_Revenue_Budgets */  left outer join
                          XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */  On T28761.PERIOD_NAME = T34877.PERIOD_NAME) left outer join
                     XXFI.XXFI_GEO_REGION_ACCUM T30728 /* Dim_Regions */  On T30728.COUNTRY_CODE = T34877.SHIP_TO_COUNTRY_CODE2) left outer join
                XXFI.XXFI_ICN_OWNERS_V T41894 /* Dim_ICN_Override */  On T34877.OVERRIDE_ICN = T41894.ICN_CODE
      where  ( T28761.QUARTER_YEAR_NAME = '2010-Q2' )
      group by T28761.QUARTER_YEAR_NAME, T28761.QUARTER_RANK, T30728.PARENT_REGION, T41894.ICN_GROUP_CODE, T41894.ICN_GROUP),
      SAWITH11 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
      from
           (select sum(SAWITH10.c1) over (partition by SAWITH10.c4)  as c1,
                     sum(SAWITH10.c1) over (partition by SAWITH10.c6, SAWITH10.c7, SAWITH10.c4)  as c2,
                     SAWITH10.c3 as c3,
                     SAWITH10.c4 as c4,
                     SAWITH10.c5 as c5,
                     SAWITH10.c6 as c6,
                     SAWITH10.c7 as c7,
                     ROW_NUMBER() OVER (PARTITION BY SAWITH10.c4, SAWITH10.c6, SAWITH10.c7 ORDER BY SAWITH10.c4 ASC, SAWITH10.c6 ASC, SAWITH10.c7 ASC) as c8
                from
                     SAWITH10
           ) D1
      where  ( D1.c8 = 1 ) ),
      SAWITH12 AS (select T28761.PERIOD_NAME as c2,
           T28761.PERIOD_START_DATE as c3,
           T28761.QUARTER_RANK as c4,
           ROW_NUMBER() OVER (PARTITION BY T28761.QUARTER_RANK ORDER BY T28761.QUARTER_RANK DESC) as c5
      from
           XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */ ),
      SAWITH13 AS (select Case when case SAWITH12.c5 when 1 then SAWITH12.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH12.c5 when 1 then SAWITH12.c3 else NULL end  ASC NULLS LAST ) end as c1,
           SAWITH12.c2 as c2,
           SAWITH12.c4 as c3
      from
           SAWITH12),
      SAWITH14 AS (select min(SAWITH13.c1) over (partition by SAWITH13.c3)  as c1,
           SAWITH13.c2 as c2
      from
           SAWITH13),
      SAWITH15 AS (select distinct SAWITH14.c1 + 1 as c1,
           SAWITH14.c2 as c2
      from
           SAWITH14),
      SAWITH16 AS (select T28761.QUARTER_YEAR_NAME as c2,
           T28761.PERIOD_START_DATE as c3,
           T28761.QUARTER_RANK as c4,
           ROW_NUMBER() OVER (PARTITION BY T28761.QUARTER_RANK ORDER BY T28761.QUARTER_RANK DESC) as c5
      from
           XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */ ),
      SAWITH17 AS (select Case when case SAWITH16.c5 when 1 then SAWITH16.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH16.c5 when 1 then SAWITH16.c3 else NULL end  ASC NULLS LAST ) end as c1,
           SAWITH16.c2 as c2,
           SAWITH16.c4 as c3
      from
           SAWITH16),
      SAWITH18 AS (select distinct min(SAWITH17.c1) over (partition by SAWITH17.c3)  as c1,
           SAWITH17.c2 as c2
      from
           SAWITH17),
      SAWITH19 AS (select sum(case  when T37838.POL_VERSION_FLAG = 'C' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 then nvl(T37838.AMOUNT , 0) when T37838.PERIOD_NAME <> 'March-13' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 and T37838.POL_VERSION_FLAG is null then nvl(T37838.AMOUNT , 0) else 0 end ) as c1,
           T30728.PARENT_REGION as c2
      from
           SAWITH15 left outer join (
                     XXFI.XXFI_REVFCST_POL_REPORTING_V T37838 /* Fact_POL_Snapshot */  left outer join
                     XXFI.XXFI_GEO_REGION_ACCUM T30728 /* Dim_Regions */  On T30728.COUNTRY_CODE = T37838.SHIP_TO_COUNTRY_CODE2) On SAWITH15.c2 = T37838.PERIOD_NAME,
           SAWITH18
      where  ( SAWITH18.c1 = SAWITH15.c1 and SAWITH18.c2 = '2010-Q2' and (T37838.PROSPECT_NUMBER is null or T37838.PROSPECT_NUMBER not like '%Budget%') )
      group by T30728.PARENT_REGION),
      SAWITH20 AS (select T28761.PERIOD_NAME as c2,
           T28761.PERIOD_START_DATE as c3,
           T28761.QUARTER_RANK as c4,
           ROW_NUMBER() OVER (PARTITION BY T28761.QUARTER_RANK ORDER BY T28761.QUARTER_RANK DESC) as c5
      from
           XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */ ),
      SAWITH21 AS (select Case when case SAWITH20.c5 when 1 then SAWITH20.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH20.c5 when 1 then SAWITH20.c3 else NULL end  ASC NULLS LAST ) end as c1,
           SAWITH20.c2 as c2,
           SAWITH20.c4 as c3
      from
           SAWITH20),
      SAWITH22 AS (select min(SAWITH21.c1) over (partition by SAWITH21.c3)  as c1,
           SAWITH21.c2 as c2
      from
           SAWITH21),
      SAWITH23 AS (select distinct SAWITH22.c1 + 1 as c1,
           SAWITH22.c2 as c2
      from
           SAWITH22),
      SAWITH24 AS (select T28761.QUARTER_YEAR_NAME as c2,
           T28761.PERIOD_START_DATE as c3,
           T28761.QUARTER_RANK as c4,
           ROW_NUMBER() OVER (PARTITION BY T28761.QUARTER_RANK ORDER BY T28761.QUARTER_RANK DESC) as c5
      from
           XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */ ),
      SAWITH25 AS (select Case when case SAWITH24.c5 when 1 then SAWITH24.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case SAWITH24.c5 when 1 then SAWITH24.c3 else NULL end  ASC NULLS LAST ) end as c1,
           SAWITH24.c2 as c2,
           SAWITH24.c4 as c3
      from
           SAWITH24),
      SAWITH26 AS (select distinct min(SAWITH25.c1) over (partition by SAWITH25.c3)  as c1,
           SAWITH25.c2 as c2
      from
           SAWITH25),
      SAWITH27 AS (select sum(case  when T37838.POL_VERSION_FLAG = 'C' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 then nvl(T37838.AMOUNT , 0) when T37838.PERIOD_NAME <> 'March-13' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 and T37838.POL_VERSION_FLAG is null then nvl(T37838.AMOUNT , 0) else 0 end ) as c1
      from
           SAWITH23 left outer join XXFI.XXFI_REVFCST_POL_REPORTING_V T37838 /* Fact_POL_Snapshot */  On SAWITH23.c2 = T37838.PERIOD_NAME,
           SAWITH26
      where  ( SAWITH26.c1 = SAWITH23.c1 and SAWITH26.c2 = '2010-Q2' and (T37838.PROSPECT_NUMBER is null or T37838.PROSPECT_NUMBER not like '%Budget%') ) ),
      SAWITH28 AS (select sum(case  when T37838.POL_VERSION_FLAG = 'C' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 then nvl(T37838.AMOUNT , 0) when T37838.PERIOD_NAME <> 'March-13' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 and T37838.POL_VERSION_FLAG is null then nvl(T37838.AMOUNT , 0) else 0 end ) as c1,
           sum(case  when T37838.POL_VERSION_FLAG = 'C' and T37838.FORECAST_PROBABILITY_PERCENT >= 50 then nvl(T37838.AMOUNT , 0) when T37838.PERIOD_NAME <> 'March-13' and T37838.FORECAST_PROBABILITY_PERCENT >= 50 and T37838.POL_VERSION_FLAG is null then nvl(T37838.AMOUNT , 0) else 0 end ) as c2
      from
                XXFI.XXFI_REVFCST_POL_REPORTING_V T37838 /* Fact_POL_Snapshot */  left outer join
                XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */  On T28761.PERIOD_NAME = T37838.PERIOD_NAME
      where  ( T28761.QUARTER_YEAR_NAME = '2010-Q2' and (T37838.PROSPECT_NUMBER is null or T37838.PROSPECT_NUMBER not like '%Budget%') ) ),
      SAWITH29 AS (select sum(T34877.AMOUNT) as c1
      from
                XXFI.XXFI_REVENUE_BUDGET_ACCUM T34877 /* Fact_Revenue_Budgets */  left outer join
                XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */  On T28761.PERIOD_NAME = T34877.PERIOD_NAME
      where  ( T28761.QUARTER_YEAR_NAME = '2010-Q2' ) )
      select case  when SAWITH7.c2 is not null then SAWITH7.c2 when SAWITH9.c6 is not null then SAWITH9.c6 when SAWITH11.c3 is not null then SAWITH11.c3 end  as c1,
           case  when SAWITH9.c3 is not null then SAWITH9.c3 when SAWITH19.c2 is not null then SAWITH19.c2 when SAWITH7.c6 is not null then SAWITH7.c6 when SAWITH11.c4 is not null then SAWITH11.c4 end  as c2,
           case  when SAWITH7.c3 is not null then SAWITH7.c3 when SAWITH9.c7 is not null then SAWITH9.c7 when SAWITH11.c5 is not null then SAWITH11.c5 end  as c3,
           SAWITH9.c5 / nullif( 1000, 0) as c4,
           SAWITH7.c1 / nullif( 1000, 0) as c5,
           SAWITH11.c2 / nullif( 1000, 0) as c6,
           nvl(SAWITH9.c4 , 0) / nullif( nvl(SAWITH11.c2 , 0), 0) * 100 as c7,
           case  when SAWITH7.c4 is not null then SAWITH7.c4 when SAWITH9.c8 is not null then SAWITH9.c8 when SAWITH11.c6 is not null then SAWITH11.c6 end  as c16,
           case  when SAWITH11.c7 is not null then SAWITH11.c7 when SAWITH9.c9 is not null then SAWITH9.c9 when SAWITH7.c5 is not null then SAWITH7.c5 end  as c17,
           SAWITH27.c1 as c19,
           SAWITH28.c2 as c20,
           SAWITH29.c1 as c21,
           SAWITH28.c1 as c22,
           SAWITH19.c1 as c23,
           SAWITH9.c2 as c24,
           SAWITH11.c1 as c25,
           SAWITH9.c1 as c26
      from
           (
                (
                     SAWITH7 full outer join SAWITH9 On SAWITH7.c5 = SAWITH9.c9 and nvl(SAWITH7.c4 , 88.0) = nvl(SAWITH9.c8 , 88.0) and nvl(SAWITH7.c4 , 99.0) = nvl(SAWITH9.c8 , 99.0) and nvl(SAWITH7.c6 , 'q') = nvl(SAWITH9.c3 , 'q') and nvl(SAWITH7.c6 , 'z') = nvl(SAWITH9.c3 , 'z')) full outer join SAWITH11 On SAWITH11.c7 = case  when SAWITH7.c5 is not null then SAWITH7.c5 when SAWITH9.c9 is not null then SAWITH9.c9 end  and nvl(SAWITH11.c4 , 'q') = nvl(case  when SAWITH7.c6 is not null then SAWITH7.c6 when SAWITH9.c3 is not null then SAWITH9.c3 end  , 'q') and nvl(SAWITH11.c4 , 'z') = nvl(case  when SAWITH7.c6 is not null then SAWITH7.c6 when SAWITH9.c3 is not null then SAWITH9.c3 end  , 'z') and nvl(SAWITH11.c6 , 88.0) = nvl(case  when SAWITH7.c4 is not null then SAWITH7.c4 when SAWITH9.c8 is not null then SAWITH9.c8 end  , 88.0) and nvl(SAWITH11.c6 , 99.0) = nvl(case  when SAWITH7.c4 is not null then SAWITH7.c4 when SAWITH9.c8 is not null then SAWITH9.c8 end  , 99.0)) full outer join SAWITH19 On nvl(SAWITH19.c2 , 'q') = nvl(case  when SAWITH7.c6 is not null then SAWITH7.c6 when SAWITH9.c3 is not null then SAWITH9.c3 when SAWITH11.c4 is not null then SAWITH11.c4 end  , 'q') and nvl(SAWITH19.c2 , 'z') = nvl(case  when SAWITH7.c6 is not null then SAWITH7.c6 when SAWITH9.c3 is not null then SAWITH9.c3 when SAWITH11.c4 is not null then SAWITH11.c4 end  , 'z'),
           SAWITH27,
           SAWITH28,
           SAWITH29
      order by c2


      +++:cfa20000:cfa20015:----2013/08/14 10:31:12

      -------------------- Query Status: Successful Completion


      +++:cfa20000:cfa20015:----2013/08/14 10:31:12

      -------------------- Rows 21, bytes 34272 retrieved from database query id: <<5087545>>


      +++:cfa20000:cfa20015:----2013/08/14 10:31:12

      -------------------- Physical query response time 27 (seconds), id <<5087545>>


      +++:cfa20000:cfa20015:----2013/08/14 10:31:12

      -------------------- Physical Query Summary Stats: Number of physical queries 1, Cumulative time 27, DB-connect time 0 (seconds)


      +++:cfa20000:cfa20015:----2013/08/14 10:31:12

      -------------------- Rows returned to Client 17

       

       

       

       

      ---------------------------------------------  11g Query ----------------------------------------------------------

      Sending query to database named EBS Rapid Data Store (id: <<2779207>>), connection pool named EBS XXFI Connection Pool, logical request hash 1334563, physical request hash 292e1532: [[
      WITH
      OBICOMMON0 AS (select T28761.PERIOD_NAME as c2,
           T28761.PERIOD_START_DATE as c3,
           T28761.QUARTER_RANK as c4,
           ROW_NUMBER() OVER (PARTITION BY T28761.QUARTER_RANK ORDER BY T28761.QUARTER_RANK DESC) as c5,
           T28761.QUARTER_YEAR_NAME as c6
      from
           XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */ ),
      SAWITH0 AS (select Case when case D1.c5 when 1 then D1.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case D1.c5 when 1 then D1.c3 else NULL end  ASC NULLS LAST ) end as c1,
           D1.c2 as c2,
           D1.c4 as c3
      from
           OBICOMMON0 D1),
      SAWITH1 AS (select min(D1.c1) over (partition by D1.c3)  as c1,
           D1.c2 as c2
      from
           SAWITH0 D1),
      SAWITH2 AS (select distinct D1.c1 + 1 as c1,
           D1.c2 as c2
      from
           SAWITH1 D1),
      SAWITH3 AS (select Case when case D1.c5 when 1 then D1.c3 else NULL end  is not null then Rank() OVER ( ORDER BY case D1.c5 when 1 then D1.c3 else NULL end  ASC NULLS LAST ) end as c1,
           D1.c6 as c2,
           D1.c4 as c3
      from
           OBICOMMON0 D1),
      SAWITH4 AS (select distinct min(D1.c1) over (partition by D1.c3)  as c1,
           D1.c2 as c2,
           D1.c3 as c3
      from
           SAWITH3 D1),
      SAWITH5 AS (select sum(case  when T37838.POL_VERSION_FLAG = 'C' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 then nvl(T37838.AMOUNT , 0) when T37838.PERIOD_NAME <> 'March-13' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 and T37838.POL_VERSION_FLAG is null then nvl(T37838.AMOUNT , 0) else 0 end ) as c1,
           D4.c2 as c2,
           T41894.ICN_GROUP as c3,
           T30728.PARENT_REGION as c4,
           D4.c3 as c5,
           T41894.ICN_GROUP_CODE as c6
      from
           SAWITH2 D6 left outer join (
                (
                          XXFI.XXFI_REVFCST_POL_REPORTING_V T37838 /* Fact_POL_Snapshot */  left outer join
                          XXFI.XXFI_GEO_REGION_ACCUM T30728 /* Dim_Regions */  On T30728.COUNTRY_CODE = T37838.SHIP_TO_COUNTRY_CODE2) left outer join
                     XXFI.XXFI_ICN_OWNERS_V T41894 /* Dim_ICN_Override */  On T37838.OVERRIDE_ICN = T41894.ICN_CODE) On D6.c2 = T37838.PERIOD_NAME,
           SAWITH4 D4
      where  ( D4.c1 = D6.c1 and D4.c2 = '2010-Q2' and (T37838.PROSPECT_NUMBER is null or T37838.PROSPECT_NUMBER not like '%Budget%') )
      group by T30728.PARENT_REGION, T41894.ICN_GROUP_CODE, T41894.ICN_GROUP, D4.c2, D4.c3),
      SAWITH6 AS (select sum(case  when T37838.POL_VERSION_FLAG = 'C' and T37838.FORECAST_PROBABILITY_PERCENT >= 50 then nvl(T37838.AMOUNT , 0) when T37838.PERIOD_NAME <> 'March-13' and T37838.FORECAST_PROBABILITY_PERCENT >= 50 and T37838.POL_VERSION_FLAG is null then nvl(T37838.AMOUNT , 0) else 0 end ) as c1,
           sum(case  when T37838.POL_VERSION_FLAG = 'C' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 then nvl(T37838.AMOUNT , 0) when T37838.PERIOD_NAME <> 'March-13' and T37838.FORECAST_PROBABILITY_PERCENT >= 75 and T37838.POL_VERSION_FLAG is null then nvl(T37838.AMOUNT , 0) else 0 end ) as c2,
           T28761.QUARTER_YEAR_NAME as c3,
           T41894.ICN_GROUP as c4,
           T30728.PARENT_REGION as c5,
           T28761.QUARTER_RANK as c6,
           T41894.ICN_GROUP_CODE as c7
      from
           (
                (
                          XXFI.XXFI_REVFCST_POL_REPORTING_V T37838 /* Fact_POL_Snapshot */  left outer join
                          XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */  On T28761.PERIOD_NAME = T37838.PERIOD_NAME) left outer join
                     XXFI.XXFI_GEO_REGION_ACCUM T30728 /* Dim_Regions */  On T30728.COUNTRY_CODE = T37838.SHIP_TO_COUNTRY_CODE2) left outer join
                XXFI.XXFI_ICN_OWNERS_V T41894 /* Dim_ICN_Override */  On T37838.OVERRIDE_ICN = T41894.ICN_CODE
      where  ( T28761.QUARTER_YEAR_NAME = '2010-Q2' and (T37838.PROSPECT_NUMBER is null or T37838.PROSPECT_NUMBER not like '%Budget%') )
      group by T28761.QUARTER_YEAR_NAME, T28761.QUARTER_RANK, T30728.PARENT_REGION, T41894.ICN_GROUP_CODE, T41894.ICN_GROUP),
      SAWITH7 AS (select sum(T34877.AMOUNT) as c1,
           T28761.QUARTER_YEAR_NAME as c2,
           T41894.ICN_GROUP as c3,
           T30728.PARENT_REGION as c4,
           T28761.QUARTER_RANK as c5,
           T41894.ICN_GROUP_CODE as c6
      from
           (
                (
                          XXFI.XXFI_REVENUE_BUDGET_ACCUM T34877 /* Fact_Revenue_Budgets */  left outer join
                          XXFI.XXFI_GL_FISCAL_MONTHS_V T28761 /* Dim_Periods */  On T28761.PERIOD_NAME = T34877.PERIOD_NAME) left outer join
                     XXFI.XXFI_GEO_REGION_ACCUM T30728 /* Dim_Regions */  On T30728.COUNTRY_CODE = T34877.SHIP_TO_COUNTRY_CODE2) left outer join
                XXFI.XXFI_ICN_OWNERS_V T41894 /* Dim_ICN_Override */  On T34877.OVERRIDE_ICN = T41894.ICN_CODE
      where  ( T28761.QUARTER_YEAR_NAME = '2010-Q2' )
      group by T28761.QUARTER_YEAR_NAME, T28761.QUARTER_RANK, T30728.PARENT_REGION, T41894.ICN_GROUP_CODE, T41894.ICN_GROUP),
      SAWITH8 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.c18 as c18,
           D1.c19 as c19,
           D1.c20 as c20,
           D1.c21 as c21,
           D1.c22 as c22
      from
           (select 0 as c1,
                     case  when D1.c2 is not null then D1.c2 when D2.c3 is not null then D2.c3 when D3.c2 is not null then D3.c2 end  as c2,
                     case  when D1.c3 is not null then D1.c3 when D2.c4 is not null then D2.c4 when D3.c3 is not null then D3.c3 end  as c3,
                     case  when D1.c4 is not null then D1.c4 when D2.c5 is not null then D2.c5 when D3.c4 is not null then D3.c4 end  as c4,
                     case  when D1.c5 is not null then D1.c5 when D2.c6 is not null then D2.c6 when D3.c5 is not null then D3.c5 end  as c5,
                     nvl(D2.c2 , 0) / nullif( nvl(D3.c1 , 0), 0) * 100 as c6,
                     D3.c1 / 1000 as c7,
                     D1.c1 / 1000 as c8,
                     D2.c1 / 1000 as c9,
                     case  when D1.c6 is not null then D1.c6 when D2.c7 is not null then D2.c7 when D3.c6 is not null then D3.c6 end  as c18,
                     D2.c2 as c19,
                     D3.c1 as c20,
                     D1.c1 as c21,
                     D2.c1 as c22,
                     ROW_NUMBER() OVER (PARTITION BY case  when D1.c2 is not null then D1.c2 when D2.c3 is not null then D2.c3 when D3.c2 is not null then D3.c2 end , case  when D1.c3 is not null then D1.c3 when D2.c4 is not null then D2.c4 when D3.c3 is not null then D3.c3 end , case  when D1.c4 is not null then D1.c4 when D2.c5 is not null then D2.c5 when D3.c4 is not null then D3.c4 end , case  when D1.c5 is not null then D1.c5 when D2.c6 is not null then D2.c6 when D3.c5 is not null then D3.c5 end , case  when D1.c6 is not null then D1.c6 when D2.c7 is not null then D2.c7 when D3.c6 is not null then D3.c6 end  ORDER BY case  when D1.c2 is not null then D1.c2 when D2.c3 is not null then D2.c3 when D3.c2 is not null then D3.c2 end  ASC, case  when D1.c3 is not null then D1.c3 when D2.c4 is not null then D2.c4 when D3.c3 is not null then D3.c3 end  ASC, case  when D1.c4 is not null then D1.c4 when D2.c5 is not null then D2.c5 when D3.c4 is not null then D3.c4 end  ASC, case  when D1.c5 is not null then D1.c5 when D2.c6 is not null then D2.c6 when D3.c5 is not null then D3.c5 end  ASC, case  when D1.c6 is not null then D1.c6 when D2.c7 is not null then D2.c7 when D3.c6 is not null then D3.c6 end  ASC) as c23
                from
                     (
                          SAWITH5 D1 full outer join SAWITH6 D2 On D1.c6 = D2.c7 and  SYS_OP_MAP_NONNULL(D1.c4) = SYS_OP_MAP_NONNULL(D2.c5)  and  SYS_OP_MAP_NONNULL(D1.c5) = SYS_OP_MAP_NONNULL(D2.c6) ) full outer join SAWITH7 D3 On D3.c6 = case  when D1.c6 is not null then D1.c6 when D2.c7 is not null then D2.c7 end  and  SYS_OP_MAP_NONNULL(D3.c4) = SYS_OP_MAP_NONNULL(case  when D1.c4 is not null then D1.c4 when D2.c5 is not null then D2.c5 end )  and  SYS_OP_MAP_NONNULL(D3.c5) = SYS_OP_MAP_NONNULL(case  when D1.c5 is not null then D1.c5 when D2.c6 is not null then D2.c6 end )
           ) D1
      where  ( D1.c23 = 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.c18 as c19,
           D1.c19 as c21,
           D1.c20 as c22,
           D1.c21 as c23,
           D1.c22 as c24
      from
           SAWITH8 D1
      order by c4, c2, c5, c19, c3

      ]]
      [2013-08-14T10:38:15.000-05:00] [OracleBIServerComponent] [TRACE:2] [USER-34] [] [ecid: 54a0696aeaefab88:-d74da91:1406506ef2a:-8000-00000000000151e6,0:1:9:6:1] [tid: a093e700] [requestid: cc480014] [sessionid: cc480000] [username: ] -------------------- Query Status: Successful Completion [[

      ]]
      [2013-08-14T10:38:15.000-05:00] [OracleBIServerComponent] [TRACE:2] [USER-26] [] [ecid: 54a0696aeaefab88:-d74da91:1406506ef2a:-8000-00000000000151e6,0:1:9:6:1] [tid: a093e700] [requestid: cc480014] [sessionid: cc480000] [username: ] -------------------- Rows 17, bytes 27200 retrieved from database query id: <<2779207>>

        • 1. Re: 11g SQL query syntax/results differ from 10g
          user10615659

          check the report for the columns which has incorrect values for any calculation, null handling.

          also check whether there is a difference in logical SQL between 10g and 11g.

          also check whether they are pointing to same db.

          check for null handling or default value not being set in 11g.

          try rebuilding the same report, to check the metrics individually verify they match with 10g.