0

I am trying to remove duplicate employee records by creating a column that will number each row for an individual 'emp_name', then removing all but the first entry of each. I seem to be missing whatever is required that will allow me to use the new 'Row_Num' I've created though. Any help would be appreciated.

select 
    a.emp_name, 
    a.emp_lastname || ',' ||a.EMP_FIRSTNAME, 
    a.bal_name, 
    ROUND(a.empbal_value),
    b.EMP_TERMINATION_DATE,
    ROW_NUMBER() OVER (
        Partition by a.emp_name
        Order By a.emp_name)
        Row_Num

FROM workbrain.view_employee_balance a
INNER JOIN workbrain.EMPLOYEE_HISTORY b on b.EMP_NAME = a.EMP_NAME

WHERE a.bal_name  in ('CARMEN ACCUMULATED HOURS')
    and a.empbal_value > 0
    and b.EMP_TERMINATION_DATE >= '01-JAN-2100'
    and Row_Num = '1'

Here's the output without the Row_Num = '1' above. Once I enter that I get the error that "ROW_NUM": invalid identifier.

enter image description here

Bill Kelly
  • 1
  • 2
    Your `where` cause isn't aware of calculations you have made in your `select` clause. You could wrap this in a subquery. `SELECT * FROM (your query) sq WHERE Row_Num = 1` – EdmCoff May 08 '23 at 22:07

0 Answers0