4 Replies Latest reply on Mar 27, 2019 8:36 PM by Avinash Pentyala

    OBIEE 12c - BI Server generating ROW_NUMBER() in query

    Avinash Pentyala

      Hi All, I am using sample snowflake schema, while creating report BI generating ROW_NUMBER()  over on each column.

      kindly suggest how to avoid/disable this??

       

      sample query

       

      select

      T81504."ACC_OPN_DT" as c8,

      T81504."PROD_ID" as c9,     

         T81504."CUST_ID" as c10,   

      ROW_NUMBER() OVER (PARTITION BY T81495."SEGMENT_OWNER", case  when T81504."PROD_ID" in ('0040004', '0040017', '0040029', '0040036', '0040058', '0040059', '0040067', '0040068') then T81504."CUST_ID" end    ORDER BY T81495."SEGMENT_OWNER" DESC, case  when T81504."PROD_ID" in ('0040004', '0040017', '0040029', '0040036', '0040058', '0040059', '0040067', '0040068') then T81504."CUST_ID" end  DESC) as c11,

        case  when T81504."PROD_ID" in ('0040004', '0040017', '0040029', '0040036', '0040058', '0040059', '0040067', '0040068') then T81504."CUST_ID" end  as c12,     

          ROW_NUMBER() OVER (PARTITION BY T81495."SEGMENT_OWNER", case  when T81504."PROD_ID" in ('0040010', '0040048', '0040055', '0040056', '0040057') then T81504."CUST_ID" end  ORDER BY T81495."SEGMENT_OWNER" DESC, case  when T81504."PROD_ID" in ('0040010', '0040048', '0040055', '0040056', '0040057') then T81504."CUST_ID" end  DESC) as c13,

          case  when T81504."PROD_ID" in ('0040010', '0040048', '0040055', '0040056', '0040057') then T81504."CUST_ID" end  as c14

            ROW_NUMBER() OVER (PARTITION BY T81495."SEGMENT_OWNER", case  when T81504."PROD_ID" = '0040001' then T81504."CUST_ID" end  ORDER BY T81495."SEGMENT_OWNER" DESC, case  when T81504."PROD_ID" = '0040001' then T81504."CUST_ID" end  DESC) as c15,  

      case  when T81504."PROD_ID" = '0040001' then T81504."CUST_ID" end  as c16     

                              from   

              (     "T_MARKET_CUSTOMER" T81472 /* Dim_Customers */ inner join "MKTG_SEGMENT" T81495 /* Dim_Customer Segments */ 

      On T81472."CUST_ID" = T81495."SEGMENT_PRTY_ID") left outer join                                        "MKTG_AGMT" T81504 /* Fact_Agmt */

      On T81472."CUST_ID" = T81504."CUST_ID"         

                    ) D1