Hello,
I'm trying to implement Oracle RANK function (the analytical version, see
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm).
This is my first try at modifying H2 sources, so I'm not familiar with its
internals.
I'm working with the following testcase:
create table testRank (id number(9), txt1 varchar(16), txt2 varchar(16),
num number(9, 0));
insert into testRank(id, txt1, txt2, num) values(1, 'a', 'c', 3);
insert into testRank(id, txt1, txt2, num) values(2, 'b', 'a', 2);
insert into testRank(id, txt1, txt2, num) values(3, 'a', 'a', 1);
insert into testRank(id, txt1, txt2, num) values(4, null, null, null);
SELECT id,txt1,txt2,num,RANK () OVER (ORDER BY txt1 ASC NULLS LAST) rnk
FROM testRank WHERE num IS NULL OR num<>2 ORDER BY id ASC;
ID txt1 txt2 num rnk
1 a c 3 1
3 a a 1 1
4 3
I've successfully defined a Rank function and parsed its parameters. Now,
to compute the rank values, my idea is basically
to execute a statement derived from initial SELECT but with order defined
by RANK like this:
SELECT txt1 FROM testRank WHERE num IS NULL OR num<>2 ORDER BY txt1 ASC
NULLS LAST
Then I build an index txt1=>rank with the result and so for each row of the
main select, I am able to return its rank.
The problem is when I'm trying to execute the derived select. I need to
copy some elements from the main select.
For the moment, my code is like this:
public class Rank extends Expression {
public Rank(Select mainSelect, ArrayList<SelectOrderBy> rankOrderList) {
this.mainSelect = mainSelect;
this.rankOrderList = rankOrderList;
}
@Override
public Value getValue(Session session) {
// populate rankValues first time
if (rankValues == null) {
rankValues = executeRankQuery(session);
}
...
}
private Map<ValueArray, Integer> executeRankQuery(Session session) {
Select rankSelect = new Select(session);
for (TableFilter tf : mainSelect.getTopFilters()) {
rankSelect.addTableFilter(tf, true);
}
rankSelect.addCondition(mainSelect.getCondition());
rankSelect.setOrder(rankOrderList);
ArrayList<Expression> expressions = New.arrayList();
for (SelectOrderBy orderBy : rankOrderList) {
expressions.add(orderBy.expression);
}
rankSelect.setExpressions(expressions);
// execute query
rankSelect.init();
rankSelect.prepare();
ResultInterface result = rankSelect.query(0);
// compute ranks
...
}
There is no exception but mainSelect now returns only 1 row:
ID txt1 txt2 num rnk
1 a c 3 0
This seems to be related to the fact of reusing TableFilter and Condition
in rankSelect.
Is there another way to do this? Do I have to rebuild a SQL text statement
and let the parser build the query?? clone some elements?
Thanks,
Boris.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
