Querying employee table twice via alias in SQL Query Node

Try aliasing the columns within the SQL (e.g. EMPLOYEE.JOB_CODE as EMP_JOB_CODE, HasSuper.JOB_CODE AS SUPER_JOB_CODE). =

I have a similar query that we run to gather data on new hires' HR11 records, where I got around the circular reference by creating two aliases for the employee table...

I know it's messy, but I've included the portions of SQL statement that apply below for your reference. As you can see, there are multiple joins performed, so the first employee table alias is a.EMPLOYEE (for the employee's data) and the second is h.EMPLOYEE (for the employee's supervisor's employee number & email).

This query works like a charm for us. I hope it helps!

SET NOCOUNT ON
SELECT 'FIRST_NAME',...'SUPER_EMP_NUM','SUPER_EMAIL',...
SELECT DISTINCT ...,e.EMPLOYEE as SUPER_EMP_NUM,h.EMAIL_ADDRESS as SUPER_EMAIL,...
FROM produser.EMPLOYEE a
INNER JOIN produser.DEPTCODE b ON (a.COMPANY = b.COMPANY and a.DEPARTMENT = b.DEPARTMENT)
INNER JOIN produser.PAPOSITION c ON (a.COMPANY = c.COMPANY and a.DEPARTMENT = c.DEPARTMENT and a.JOB_CODE = c.JOB_CODE and a.R_POSITION = c.R_POSITION and a.PAY_GRADE = c.PAY_GRADE and a.SUPERVISOR = c.SUPERVISOR)
INNER JOIN produser.JOBCODE d ON (d.COMPANY = c.COMPANY and a.JOB_CODE = d.JOB_CODE)
INNER JOIN produser.HRSUPER e ON (a.COMPANY = e.COMPANY and a.SUPERVISOR = e.CODE)
INNER JOIN produser.EMSTATUS f ON ((a.COMPANY = f.COMPANY) and (a.EMP_STATUS = f.EMP_STATUS) and ((a.EMP_STATUS != 'AI' and a.EMP_STATUS != 'AW' and a.EMP_STATUS != 'CN' and a.EMP_STATUS != 'PR') and ((a.EMP_STATUS like 'A%') or (a.EMP_STATUS like 'L%') or (a.EMP_STATUS like 'P%') or (a.EMP_STATUS = 'FT'))))
INNER JOIN produser.PAEMPLOYEE g ON ((a.COMPANY = g.COMPANY) and (a.EMPLOYEE = g.EMPLOYEE))
INNER JOIN produser.EMPLOYEE h ON ((a.COMPANY = h.COMPANY) and (e.EMPLOYEE = h.EMPLOYEE))
-- To use a variable that is defined in your batch file: '$(MyVariableName)'
WHERE ...
ORDER BY SUPER_EMAIL,a.COMPANY,EMPLOYEE_NBR

Here is another example of an SQL query that gets both the employee info and their supervisor's info. It uses different alias names for the employee info vs supervisor info:
----------------------------
select trim(e.EMPLOYEE) "EMPNO",
trim(e.FIRST_NAME) "EMP_FIRST_NAME",
trim(e.LAST_NAME) "EMP_LAST_NAME",

trim((select s2.employee
from devlaw.hrsuper s2
where s2.code = e.supervisor)) "SUP_EMPNO",

trim((select first_name
from devlaw.employee e2
where e2.employee =
(select s2.employee
from devlaw.hrsuper s2
where s2.code = e.supervisor))) "SUP_FIRST_NAME",

trim((select last_name
from devlaw.employee e2
where e2.employee =
(select s2.employee
from devlaw.hrsuper s2
where s2.code = e.supervisor))) "SUP_LAST_NAME"

from devlaw.employee e

Posted By John Henley on 06/13/2011 12:08 PM
Try aliasing the columns within the SQL (e.g. EMPLOYEE.JOB_CODE as EMP_JOB_CODE, HasSuper.JOB_CODE AS SUPER_JOB_CODE). =

This allowed me to achieve what I needed to. This is what the SQL statement ended up looking like:
SELECT EMPLOYEE.EMPLOYEE AS "EMP_EMP",
       EMPLOYEE.LAST_NAME AS "EMP_LNAME",
       EMPLOYEE.FIRST_NAME AS "EMP_FNAME",
       EMPLOYEE.EMAIL_ADDRESS AS "EMP_EMAIL",
       EMPLOYEE.EMP_STATUS AS "EMP_STATUS",
       JOBCODE.DESCRIPTION AS "EMP_JOB_DESC",
       EMPLOYEE.PROCESS_LEVEL AS "EMP_PROC_LVL",
       EMPLOYEE.DEPARTMENT AS "EMP_DEPT",
       DEPTCODE.R_NAME AS "EMP_DEPT_NAME",
       EMPLOYEE.SUPERVISOR AS "EMP_SUPER",
       EMP_BOSS.EMPLOYEE AS "BOSS_EMP",
       EMP_BOSS.LAST_NAME AS "BOSS_LNAME",
       EMP_BOSS.FIRST_NAME AS "BOSS_FNAME",
       EMP_BOSS.EMAIL_ADDRESS AS "BOSS_EMAIL"
FROM  EMPLOYEE,
       EMPLOYEE EMP_BOSS,
       JOBCODE,
       DEPTCODE,
       HRSUPER
WHERE (HRSUPER.CODE = EMPLOYEE.SUPERVISOR)
   AND (HRSUPER.EMPLOYEE = EMP_BOSS.EMPLOYEE)
   AND (EMPLOYEE.JOB_CODE = JOBCODE.JOB_CODE)
   AND (EMPLOYEE.DEPARTMENT = DEPTCODE.DEPARTMENT)
   AND EMPLOYEE.EMP_STATUS NOT LIKE 'T%'

Thanks for the help everybody.