Use of Analytical Functions in Join Index

Database
Enthusiast

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?

1 REPLY
Supporter

Re: Use of Analytical Functions in Join Index

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...