No "Group by", "Having", "Order by" key words in GEMFIRE OQL?

[johnson_edocom]

Hi guys,

I am a newer for GEMFIRE OQL. Today I tried to write a quite simple GEMFIRE OQL statement, like the following

select a.bigcustno,
       a.bigcustname,
       a.sys_source,
       a.province_no,
       a.cust_id,
       nvl(sum(a.income_1), 0) income_1,
       nvl(sum(a.income_7), 0) income_7,
  from t_mon_inc_report_etl a
 where a.is_sanhu = '0'
   and a.area_no is not null
   and lower(a.area_no) <> 'null'
   and a.parent_id is not null
   and a.trade is not null
   and a.oper_time >= '201101'
   and a.oper_time <= '201109'
 group by a.bigcustno,
          a.bigcustname,
          a.sys_source,
          a.province_no,
          a.cust_id
 order by total_income desc

However, I found the keywords "group by", "order by", "sum" are not supported by GEMFIRE OQL. Could anyone point out some solution for this?

Thanks a lot

[anilgm]

GemFire OQL engine supports "ORDER BY", but "GROUP BY" and "HAVING" is not yet supported.

Application has to write their own custom code to achieve the "Group By" functionality.

The custom code can be written by:
- Executing query and grouping the required values from the query result or
- As part of the GemFire's Function execution service.

E.g.:
If an aggregate function "SUM" needs to be performed based on "customer" on customer orders.
- With Order object that has "total" and "customer" fields.
- And is stored as value in "Orders" region.

// Execute OQL query with order-by customer field.
SelectResults sr = Select * from /Orders o order by o.customer

// Iterate through the query result and perform sum on the "total" field based on the "customer" value.

This can also be done as part of Function Execution which will be efficient on Partitioned regions (where the business application will be applied on the local node). This is explained in "Function Execution" chapter of GemFire "Developer Guide".

[Udo]

@johnson_edocom Have you looked at SQLFire yet? It does have a more complete SQL language definition and support.

OQL was not meant to be a 1:1 conversion to SQL and thus there would be some limitations.

As @anilgm said, there are other ways to approach this problem. Another approach could be...

You can have another region, which would update statistical data for each of the fields you are trying to group on. This can be achieved using CacheListeners on the t_mon_inc_report_etl Region. On a create or update of entry into this region, it would create/update the data within the custom statistical region which would provide you the "summing" functionality.

This way you can still run your OQL query over a set of dates but you should see a significant performance increase on querying ability.