How do I evaluate values between rows in the transform section?

Toad Data Point
1

After running the query, here are the results:

Field1 Field2 Calculated_Field3
AAA 55 0
AAA 66 1
BBB 55 0
BBB 55 0
BBB 66 1

The values in Calculated_Field3 are determined by this logic:
If (Field1 != Next_Row(Field1) ) Then 1 Else 0

Next_Row = The next row value in Field1

This is similar to the LAG/LEAD Oracle function. However, I do not want to use the LAG/LEAD funciton.

Any thougts on how I can accomplish this in the “Transform and Cleanse” or any other way without using the LAG/LEAD function?

2

"However, I do not want to use the LAG/LEAD funciton."
Why not? You should want to use the LAG/LEAD function. That's the best way.

In any case, here's a way to do it. (I added another row to your sample data).

create table mytable (field1 varchar2 (3), field2 number (2)) ;

insert into mytable (field1, field2) values ('BBB', 55) ;
insert into mytable (field1, field2) values ('CCC', 41) ;
insert into mytable (field1, field2) values ('AAA', 66) ;
insert into mytable (field1, field2) values ('BBB', 66) ;
insert into mytable (field1, field2) values ('AAA', 55) ;
insert into mytable (field1, field2) values ('BBB', 55) ;
commit ;

select a.field1 as f1, a.field2 as f2,
(select min (b.field1) from mytable b
where (b.field1 = a.field1 and b.field2 >= a.field2 and b.rowid != a.rowid)
or (b.field1 > a.field1)
) as nxt
from mytable a
order by a.field1, a.field2 ;

F1 F2 NXT

AAA 55 AAA
AAA 66 BBB
BBB 55 BBB
BBB 55 BBB
BBB 66 CCC
CCC 41

Using the NXT field to derive the value of the requested Calculated_Field3 is left as an exercise to the alert reader.

3

We will look into having these functions as a part of our Data Cleansing suite. Create CR 109331.

4

Thank you Ryan. When dealing with large data set and queries that take long to return values, it is best to do analytic locally after fetching the raw data.