Implementing the GROUP BY oracle query causing: not a GROUP BY expression in Ruby-CodePudding

My RUBY code executes a oracle query in the following way, but I seem to be getting the error:

Java::JavaSql::SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression

SELECT
  "REQUESTS".*
FROM
  "REQUESTS"
WHERE
  (
    customer_id = 1
    AND request_method != 'OPTIONS'
    AND request_time BETWEEN TO_TIMESTAMP('2021-10-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND TO_TIMESTAMP(
      '2021-11-05 23:59:59.000999',
      'YYYY-MM-DD HH24:MI:SS.FF'
    )
  )
GROUP BY
  "REQUESTS"."REQUEST_TIME"

Initially the code which is translated into the above mentioned select query is:

requests = Request.where("customer_id = ? AND request_method != ? AND request_time BETWEEN TO_TIMESTAMP(?,'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP(?,'YYYY-MM-DD HH24:MI:SS.FF')", customer.id, 'OPTIONS', start_time, end_time).group('date(request_time'))

The

.group('date(request_time') is translated in oracle to: GROUP BY date(request_time)

but it didn't seem to work either which was the original query, and the reason is because Oracle doesn't have this functionality , so I changed it and have been trying in differnt ways but can't seem to figure out why the group by expression wont work.

CodePudding user response：

select * means "select all columns".

Group by clause says group by request_time, which is only one column, and that just won't work.

You'll have to apply group by to ALL columns (specified one-by-one), or - simpler - use select distinct.

Basically, we use group by when there's an aggregation in select column list. If there's none, you don't group by.

What you'll really do depends on what you want to do, i.e. which result you expect.