Archives of the TeradataForum
Message Posted: Tue, 21 Mar 2006 @ 18:02:47 GMT
Subj:   Use of Ordered Analytical Functions 

From:   Anomy Anom 
< 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.
