why ordered analytical function cannot be nested?

Database
Enthusiast

why ordered analytical function cannot be nested?

Hi All,

 

I encountered error 5480 in teradata which is 'ordered analytical function cannot be nested'. My code is like:

SEL 
CASE WHEN PAGINITION = MIN(PAGINITION) OVER (PARTITION BY E.GUID, E.SESSION_SKEY, E.SESSION_START_DT, KEYWORD) THEN 1 ELSE 0 END PAGINITION_IND,
SUM(PAGINITION_IND) OVER (ORDER BY E.GUID, E.SESSION_SKEY, E.SESSION_START_DT, E.SEQNUM, RNUM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) PG_GRP

...

As you can see, after I made a column baned 'paginition_ind' using olap function, I was trying to include it in another olap function. Could anyone tell me why this nesting is not allowed and what is the best way to solve this problem?

 

Thanks a lot!

 


Accepted Solutions
Highlighted
Junior Contributor

Re: why ordered analytical function cannot be nested?

As you noticed nested OLAP functions are not allowed similar to nested Aggregates, this is a Standard SQL restriction. 

 

Rewriting them using Dervied Tables makes the syntax a bit more complex, but the actual execution plans would be the same.

For some special cases Teradata implemented the RESET WHEN clause (doesn't fit in your case), which simplifies the syntax, but still has the same plan as the nested version.

1 ACCEPTED SOLUTION
4 REPLIES
Senior Apprentice

Re: why ordered analytical function cannot be nested?

Hi Lorraine,

 

I don't know why it is not allowed, poissbly because ANSi std doesn't or because of th esequence in which the dbms does it's processing.

 

However when you find functions that cannot be combined, I usually find that the solution (or at least 'a solution') is to move one of them to a derived table. So something like:

select ...
  ,SUM(PAGINITION_IND) OVER (ORDER BY E.GUID, E.SESSION_SKEY, E.SESSION_START_DT, E.SEQNUM, RNUM ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) PG_GRP
  ...
FROM (sel ...
         CASE WHEN PAGINITION = MIN(PAGINITION) OVER (PARTITION BY E.GUID, E.SESSION_SKEY, E.SESSION_START_DT, KEYWORD) THEN 1 ELSE 0 END PAGINITION_IND
          ...) as DT1

That should allow the code to run ok.Cheers,Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: why ordered analytical function cannot be nested?

Hi Dave,

 

Thanks a lot for your reply. The derived table does work but if I have more than 1 olap function to be nested, derived table will be too complext and time consuming to execute. However this is also the solution I know so far.

 

Do other people have any ideas on it?

Senior Apprentice

Re: why ordered analytical function cannot be nested?

Hi Lorraine,

Certainly if you need to nest the functions 'two deep' then you'll need a second level of derived table.

I'm intrigued, what is your requirement?  Can you give an example?

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Junior Contributor

Re: why ordered analytical function cannot be nested?

As you noticed nested OLAP functions are not allowed similar to nested Aggregates, this is a Standard SQL restriction. 

 

Rewriting them using Dervied Tables makes the syntax a bit more complex, but the actual execution plans would be the same.

For some special cases Teradata implemented the RESET WHEN clause (doesn't fit in your case), which simplifies the syntax, but still has the same plan as the nested version.