Archives of the TeradataForum

Message Posted: Wed, 22 Mar 2006 @ 14:38:02 GMT

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

<-- Anonymously Posted: Wednesday, March 22, 2006 09:42 -->

Thanks for your responses. What I am trying to achieve is better illustrated in the following example:

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

          (case when cur.col4 <> pre.col4
                then 1
                else 0
            end
           ) +
          (case when cur.col5 <> pre.col5
                then 2
                else 0
            end
           ) +
          (case when cur.col6 <> pre.col6
                then 4
                else 0
            end
           ) +
          (case when cur.col7 <> pre.col7
                then 8
                else 0
            end
           ) +
          .
          .
          .
          (case when cur.col13 <> pre.col13
                then 512
                else 0
            end
           ) as col14

          from tableA cur
          left outer join table B pre
          on cur.col1 = pre.col1
          and cur.col2 = pre.col2
          and cur.col3 - 1 = pre.col4

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.

Btw, it only needs to compare the 2 rows at a time, for example, if a particular col1, col2 combination had 15 rows, it will compare col5 - col13 of row 15 (oldest) with row 14 and populate col14 as per the case stmt above and then row 14 and row 13 are used and so on.......

Thanks for your help.

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