What is wrong with this SQL query?

Question

Oracle

Hello
Tell me what's wrong with the request, swears at ORA-00920: invalid relational operator
SELECT * FROM rpm_future_retail where (item, location, action_date) IN
(select item, location, MAX(ACTION_DATE) action_date from rpm_future_retail
group by item, location HAVING MAX (action_date) < SYSDATE order by item)

Just in case, I will indicate here another solution option that I eventually came up with:

CREATE VIEW EX6
AS SELECT item, LOCATION, MAX(action_date) action_date FROM rpm_future_retail
GROUP BY item, location HAVING MAX(action_date) <= SYSDATE ORDER BY item;

SELECT ex6.item, ex6.location, ex6.action_date, rpm_future_retail.selling_retail actual_price FROM ex6
LEFT JOIN rpm_future_retail ON ex6.item = rpm_future_retail.item AND ex6.location = rpm_future_retail.location AND ex6.action_date = rpm_future_retail.action_date;

Thanks to all!

Answer 1

Try like this

SELECT t1.* 
  FROM rpm_future_retail t1
  INNER JOIN (
      select item, location, MAX(ACTION_DATE) action_date 
        from rpm_future_retail
        group by item, location 
        HAVING MAX(action_date) < SYSDATE order by item
    ) t2 ON (t1.item = t2.item and t1.location = t2.location and t1.action_date = t2.action_date)

Answer 2

upd. Wrong answer. You are using the in
operator incorrectly. The operator must be used for one field, and you specify several. IN Condition
where (item, location, action_date) IN