MAX() and MAX() OVER PARTITION BY produces error 3504 in Teradata Query

Logically OLAP functions are calculated after GROUP BY/HAVING, so you can only access columns in GROUP BY or columns with an aggregate function. Following looks strange, but is Standard SQL:

SELECT employee_number,
       MAX(MAX(course_completion_date)) 
           OVER (PARTITION BY course_code) AS max_course_date,
       MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code

And as Teradata allows re-using an alias this also works:

SELECT employee_number,
       MAX(max_date) 
           OVER (PARTITION BY course_code) AS max_course_date,
       MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code

I know this is a very old question, but I've been asked by someone else something similar.

I don't have TeraData, but can't you do the following?

SELECT employee_number,
       course_code,
       MAX(course_completion_date)                                     AS max_course_date,
       MAX(course_completion_date) OVER (PARTITION BY employee_number) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code

The GROUP BY now ensures one row per course per employee. This means that you just need a straight MAX() to get the max_course_date.

Before your GROUP BY was just giving one row per employee, and the MAX() OVER() was trying to give multiple results for that one row (one per course).

Instead, you now need the OVER() clause to get the MAX() for the employee as a whole. This is now legitimate because each individual row gets just one answer (as it is derived from a super-set, not a sub-set). Also, for the same reason, the OVER() clause now refers to a valid scalar value, as defined by the GROUP BY clause; employee_number.

Perhaps a short way of saying this would be that an aggregate with an OVER() clause must be a super-set of the GROUP BY, not a sub-set.

Create your query with a GROUP BY at the level that represents the rows you want, then specify OVER() clauses if you want to aggregate at a higher level.

As Ponies says in a comment, you cannot mix OLAP functions with aggregate functions.

Perhaps it's easier to get the last completion date for each employee, and join that to a dataset containing the last completion date for each of the three targeted courses.

This is an untested idea that should hopefully put you down the right path:

  SELECT employee_number,
         course_code,
         MAX(course_completion_date) AS max_date,
         lcc.LAST_COURSE_COMPLETED
    FROM employee_course_completion ecc
         LEFT JOIN (
             SELECT employee_number,
                    MAX(course_completion_date) AS LAST_COURSE_COMPLETED
               FROM employee_course_completion
              WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
         ) lcc
         ON lcc.employee_number = ecc.employee_number
   WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code, lcc.LAST_COURSE_COMPLETED