0

I have a table of rules with effective dates, which apply to specific employee types. I want to find the most recently effective rule per employee type.

One technique that was suggested was to perform a self-join, in order to first perform the grouping and selection, and then to perform the proper query having found the selected rules.

The SQL I have is as follows:

SELECT dtr.employee_type
    ,dtr.start_time
    ,dtr.end_time
FROM (
    SELECT MAX(effective_date) AS maxdate
        ,employee_type
    FROM default_time_rules
    GROUP BY employee_type
    ) AS grouped_dtr
INNER JOIN default_time_rules AS dtr ON dtr.employee_type = grouped_dtr.employee_type
    AND dtr.effective_date = grouped_dtr.maxdate;

However this gives me ORA-00933, command not properly ended.

As far as I can see I'm following the suggestion as it's written. Is there a small change I need to make for this technique to work - or perhaps a different query technique which can get the most recently effective rule per each employee type?

dandarc
  • 678
  • 3
  • 7
dfaulken
  • 476
  • 8
  • 18
  • 1
    remove `as` keywords for subqueries or tables. That's not allowed in Oracle. – Barbaros Özhan Sep 10 '18 at 17:59
  • Which `as` do you mean? `maxdate`, `grouped_dtr` or `dtr`? – dfaulken Sep 10 '18 at 18:00
  • `AS grouped_dtr` and `AS dtr` – Barbaros Özhan Sep 10 '18 at 18:01
  • The two table aliases - `AS grouped_dtr` and `AS dtr`. In Oracle the `AS` keyword is allowed (but is optional) for column aliases; but is not allowed for table aliases, including inline views. The blog you linked to see to focus on MySQL, but it is allowed in other DBs too. – Alex Poole Sep 10 '18 at 18:01
  • Thanks both. Alex Poole, the syntax error may be a duplicate for the question you indicated, but my actual question is not. Even with the syntax error resolved, the question stands about how to perform this type of query. I couldn't find a duplicate question for that. – dfaulken Sep 10 '18 at 18:08
  • The first question you asked is about the error, so it is - that is the small change you need to make this technique work. There are other techniques, but there are duplicates for those too. [Here's an example](https://stackoverflow.com/q/50065638/266304) - the techniques in those answers will work here too. – Alex Poole Sep 10 '18 at 18:15

0 Answers0