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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
