All Forums Database
Ashok.Pentapati 16 posts Joined 06/09
14 Oct 2013
Use of Analytical Functions in Join Index

Hi All,
 
Can someone tell me if we can use OLAP functions during the creation of AJI.For eg:
 
 
 

CREATE JOIN INDEX BASESQL_Z

AS

SELECT                                                                                                                                                         

a.Col1 PROGRAM,b.Col2 DISTRIBUTOR,Col3 Outlet

,b.Col4 Item,,e.WeekEndDt Week,g.ExecutionDetailTypeInd DetailType   

,b.CaseEquivQty,b.bblqty,UnitQty                                                       

,RANK() OVER(PARTITION BY a.Col1,b.Col2,b.RetailOutletSid, b.OrderableSKUSid ORDER BY  e.WeekEndDt ) ROW_NO                                      

FROM                                                

     Tab1 a INNER JOIN Tab2 b                                                  

           ON b.Col2 = a.Col2 AND b.InvoiceDt >= a.StartDts                                                   

     INNER JOIN Tab3 e                                                   

           ON  b.InvoiceDt= e.DayDt                                                 

     INNER JOIN Tab4 g                                                     

           ON  g.Col4 = b.Col4  and b.COL3 = g.col3  and a.Col1=g.Col1                                                

PRIMARY INDEX (PROGRAM,DISTRIBUTOR,OUTLET,ITEM,DetailType);

 

Can someone tell if we can use Rank function like above in the creation of Join Index?

 

ulrich 816 posts Joined 09/09
14 Oct 2013

from the manuals:
 
Aggregate Join Indexes
An aggregate join index is a join index that specifies SUM or COUNT aggregate operations. No other aggregate functions are permitted in the definition of a join index.
 
so no olap functions as well...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.