Abinitio - Rank Evaluation by Scan without transform expansion

Rank Evaluation by Scan without transform expansion

1. Same as Rank Function is SQL

Use SCAN with key deptId.

Input -> sort (dept,sal dec) -> SCAN -> Output

let int _ranking_ =0; // use global variable to track/update the count

out::scan(int)=

begin

//previous() fuction returns NULL for the first record in each group,

//so the assignment is such situations is 1.

_ranking_= first_defined(if(previous(in.sal)!=in.sal) count() else _ranking_,1);

out.*::in.*;

out.rank::_ranking_;

end;

For example Input:

deptId , empId, sal

D1, E1, 890

D1, E2, 750

D1, E9, 750

D1, E3, 895

D1, E11, 740

D2, E4, 800

D2, E5, 900

D3, E8, 500

Output:

deptId , empId, Sal, Rank Drank

D1, E1, 890 2 2

D1, E2, 750 3 3

D1, E9, 750 3 3

D1, E3, 895 1 1

D1, E11, 740 5 4

D2, E4, 800 2 2

D2, E5, 900 1 1

D3, E8, 500 1 1

For More Abinitio AWS Database content please visit my youTube channel.

@datapundit

datapundittechblogs