CODEWITHSUNDEEP
sql
Aide Aparicio
Aide Aparicio

Reputation: 1

SQL Ambiguous Column Names

SELECT DISTINCT
    OrganizationCode,
    OrganizationDescription,
    FullName,
    EmpNo,
    EmployeeStatus,
    EmploymentStatus,
    CONVERT(varchar, LastHireDate, 101) AS LastHireDate,
    CONVERT(varchar, TerminationDate, 101) AS TerminationDate,
    PositionCodeDescription,
    CONVERT(varchar, POSITIONFROMEFFECTDATE, 101) AS POSITIONFROMEFFECTDATE,
    CONVERT(varchar, POSITIONTOEFFECTDATE, 101) AS POSITIONTOEFFECTDATE,
    TerminationType,
    EmploymentStatusReason,
    CASE
        WHEN EMPLOYEEJOB_CURR.TERMINATIONDATE IS NULL 
           THEN (DATEDIFF(MONTH, EMPLOYEEJOB_CURR.LASTHIREDATE, GETDATE()))
           ELSE DATEDIFF(MONTH, EMPLOYEEJOB_CURR.LASTHIREDATE, EMPLOYEEJOB_CURR.TERMINATIONDATE) 
    END AS InService,
    CASE
        WHEN VPERSON_STATUS_POSITION.POSITIONTOEFFECTDATE > '01-01-2999' 
             AND EMPLOYEEJOB_CURR.TERMINATIONDATE IS NULL 
           THEN (DATEDIFF(MONTH, VPERSON_STATUS_POSITION.POSITIONFROMEFFECTDATE, GETDATE()))
        WHEN VPERSON_STATUS_POSITION.POSITIONTOEFFECTDATE > '01-01-2999' 
             AND EMPLOYEEJOB_CURR.TERMINATIONDATE IS NOT NULL 
           THEN (DATEDIFF(MONTH, VPERSON_STATUS_POSITION.POSITIONFROMEFFECTDATE, EMPLOYEEJOB_CURR.TERMINATIONDATE))
           ELSE DATEDIFF(MONTH, VPERSON_STATUS_POSITION.POSITIONFROMEFFECTDATE, VPERSON_STATUS_POSITION.POSITIONTOEFFECTDATE) 
    END AS [IN_POSITION],
FROM 
    EmployeeJob_Curr, Dept_fac_Curr, VPERSON_STATUS_POSITION
WHERE 
    EmployeeJob_Curr.PositionIdNo = Dept_fac_Curr.PositionIdNo 
    AND VPERSON_STATUS_POSITION.PersonIdNo = EmployeeJob_Curr.PersonIdNo
ORDER BY 
    OrganizationCode ASC

I am super new with SQL, please help.

EmpNo, EmployeeStatus, EmploymentStatus, LastHireDate, and TerminationDate all show an error

Ambiguous Column Names

after I add another view :VPERSON_STATUS_POSITION. Not sure why, before adding it, the query works just fine.

Upvotes: 0

Views: 525

Answers (1)

Stu
Stu

Reputation: 32599

You can eliminate this error by using meaningful aliases and joining your tables in a clear and concise fashion.

With the example below you would prefix each column in the select with either e. or d. or v. depending on which table contains the column - or which table you want to use if a column exists in multiple tables as it does to cause your error.

select e.OrganizationCode,
...

FROM EmployeeJob_Curr e
  join Dept_fac_Curr d on d.PositionIdNo=e.PositionIdNo
  join VPERSON_STATUS_POSITION v on v.PersonIdNo=e.PersonIdNo

Upvotes: 1

Related Questions