Grouping by day in OQL

2
Hi, I am trying to group by day to find the total amount of all the payment transactions in each day. I tried 'group by TransactionDate' but it did not group the transactions as it is supposed to, probably because the date needs to be truncated and there is no truncate function in OQL. I tried using the 'datepart' function but every time I would get ORA-00979 error. Any idea how could it be done? Thanks.
asked
1 answers
0

Grouping by date is not really a problem, just use (for example) "GROUP BY DATEPART(DAYOFYEAR, DateAttr)". The problems start when you also want to SELECT the dates that you grouped by. As it turns out, this is actually quite hard to accomplish. The obvious option is

  SELECT DateAttr
  FROM Module.Entity AS Entity
  GROUP BY DATEPART(YEAR, DateAttr), DATEPART(DAYOFYEAR, DateAttr)

But this is not accepted by the database because all GROUP BY clauses must also occur in the SELECT statement. (This is the ORA-00979 error).

The solution to this is to return the date fields in separate columns:

  SELECT DATEPART(YEAR,DateAttr) AS DateYear, 
               DATEPART(MONTH,DateAttr) AS DateMonth, 
               DATEPART(DAY,DateAttr) AS DateDay
  FROM Module.Entity AS Entity
  GROUP BY DATEPART(YEAR,DateAttr), 
                    DATEPART(MONTH,DateAttr), 
                    DATEPART(DAY,DateAttr)

If you want to have a single, formatted date field in the SELECT, you run into a different problem: there is no easy way to format the date as a String in OQL. When you manually concatenate the date fields with separators in between (e.g. '-'), the GROUP BY and SELECT clauses are not recognized by the database as the same thing anymore. The only solution at this point is to use a Java action as a data source, retrieve the data using Core.retrieveOQLDataTable() and glue the date field together yourself.

answered