sqloracle-databaseora-00979

GROUP BY with flagged value

Below query is to return flag as Y if c.LAST_UPDATED_TIMESTAMP < MAX(t.LATEST_ACTION_TIMESTAMP)

SELECT
'Y' as CAN_UPDATE, t.LATEST_ACTION_TIMESTAMP
FROM CUSTOMERS c
LEFT JOIN TRANSACTIONS t ah on (c.customer_id = t.customer_id)
WHERE
t.status_active_flag = 'Y' and  c.customer_ID ='CUST_019'
GROUP BY t.LATEST_ACTION_TIMESTAMP
HAVING c.LAST_UPDATED_TIMESTAMP < MAX(t.LATEST_ACTION_TIMESTAMP);

ORA-00979 not a GROUP BY expression encountered, understand that all columns in SELECT need to be included in GROUP BY clause. How can handle for the flagged value 'Y' in this case?

Solution

  • Column c.LAST_UPDATED_TIMESTAMP need to be added to group by part as well

    GROUP BY t.LATEST_ACTION_TIMESTAMP, c.LAST_UPDATED_TIMESTAMP

    here is a dbfiddle with a dumb example