Archives of the TeradataForum

Message Posted: Wed, 22 Mar 2006 @ 20:31:31 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Use of Ordered Analytical Functions

From:		Dieter Noeth

Anomy.Anom wrote:

So, col14 holds a bitmap of the changes in col5 - col13 between current and previous row with the same col1 and col2. This query works fine in our test environment, I was just wondering if using Ordered Analytical Functions would provide any performance benefit when used with the real data.

IMHO there's no benefit, but maybe you should try it (and post the results):

     select col1, col2, col3, col4, ....... , col13,

          (case when col4 <>
                  min(col4) over (partition by col1, col2 order by col3
                      rows between 1 preceding and 1 preceding)
              then 1
              else 0
            end
           ) +
          (case when col5 <>
                  min(col5) over (partition by col1, col2 order by col3
                      rows between 1 preceding and 1 preceding)
              then 2
              else 0
            end
     ...
     from tableA cur

But i don't know if "order by col3" retrieves the same result set...

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>