Archives of the TeradataForum

Message Posted: Tue, 21 Mar 2006 @ 18:02:47 GMT

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

<-- Anonymously Posted: Tuesday, March 21, 2006 09:58 -->

I have to perform the following transformation on my source data:

Compare the current account record with the previous account record , i.e. where the values of current col1 = previous col1 and current col2 = previous col2 and current col3 - 1 = previous col4 then output the following calculated value for col15 (smallint)

      IF current COL5 <> previous COL5
      THEN 1
      ELSE 0
    + IF current COL6 <> previous COL6
      THEN 2
      ELSE 0
    + IF current COL7 <> previous COL7
      THEN 4
      ELSE 0
    + IF current COL8 <> previous COL8
      THEN 8
      ELSE 0
    + IF current COL9 <> previous COL9
      THEN 16
      ELSE 0
    + IF current COL10 <> previousCOL10
      THEN 32
      ELSE 0
    + IF current COL11 <> previous COL11
      THEN 64
      ELSE 0
    + IF current COL12 <> previous COL12
      THEN 128
      ELSE 0
    + IF current COL13 <> previous COL13
      THEN 256
      ELSE 0
    + IF current COL14 <> previous COL14
      THEN 512
      ELSE 0

I can probably use a self join and a case statement to compute the value, however I am told that using an ordered analytical function would be more efficient. Any help on the use of Ordered Analytical Function in this scenerio would be greatly appreciated.

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