Unknown database function 'DATE_FORMAT' in OracleDb-CodePudding

Below is how my BUS_DATA table looks like

Below is my query where for a single day it picks the maximum currentSpeed and returns that row for the busId selected

SELECT busId, busName, passengersNo, speedLimit, dataDateTime, DATE_FORMAT(dataDateTime, '%Y-%m-%d') dataDate , max(currentSpeed) currentSpeed from BUS_DATA where busId = '4-3323309834' GROUP BY busId, dataDate

Below is how this query looks like when it runs

This was all working well when i was using MySQL until when i migrated to Oracle RDBMS where i am getting below errors


Unknown database function 'DATE_FORMAT'

Column 'busName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Column 'passengersNo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Column 'speedLimit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Column 'speedLimit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I have tried to modify DATE_FORMAT to to_char(dataDateTime, 'YYYY-MM-DD') and making the query to look like below but its still showing errors how can i convert the above query to Oracle RDBMS


SELECT busId, busName, passengersNo, speedLimit, dataDateTime, to_char(dataDateTime, 'YYYY-MM-DD') dataDate ,
  max(currentSpeed) currentSpeed from BUS_DATA where busId = '4-3323309834' GROUP BY busId, dataDate

dataDateTime field in my table is Timestamp

CodePudding user response：

When you use GROUP BY you either need to include the column in the GROUP BY clause or you need to use an aggregation function and you cannot use an alias declared in the SELECT clause in the GROUP BY clause:

SELECT busId,
       to_char(dataDateTime, 'YYYY-MM-DD') AS dataDate,
       max(currentSpeed) AS currentSpeed
from   BUS_DATA
where  busId = '4-3323309834'
GROUP BY
       busId,
       to_char(dataDateTime, 'YYYY-MM-DD')

If you want to add more columns then:

SELECT busId,
       busName,
       passengersNo,
       speedLimit,
       dataDateTime,
       to_char(dataDateTime, 'YYYY-MM-DD') AS dataDate ,
       max(currentSpeed) AS currentSpeed
from   BUS_DATA
where  busId = '4-3323309834'
GROUP BY
       busId,
       busName,
       passengersNo,
       speedLimit,
       dataDateTime

or:

SELECT busId,
       MAX(busName) AS busName,
       MAX(passengersNo) AS passengerNo,
       MAX(speedLimit) AS speedLimit,
       MAX(dataDateTime) AS dateDateTime,
       to_char(dataDateTime, 'YYYY-MM-DD') AS dataDate ,
       max(currentSpeed) AS currentSpeed
from   BUS_DATA
where  busId = '4-3323309834'
GROUP BY
       busId,
       to_char(dataDateTime, 'YYYY-MM-DD')

or don't use GROUP BY:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY busId, to_char(dataDateTime, 'YYYY-MM-DD')
           ORDER BY currentSpeed DESC
         ) AS rn
  from   BUS_DATA t
  where  busId = '4-3323309834'
)
WHERE rn = 1;