Field is invalid in the select list

[StacyStacy]

I'm getting the following error message:

Msg 8120, Level 16, State 1, Line 1
Column 'empper.emp_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Here's my code ... what am i doing incorrectly?
Thanks ....



SELECT empper.emp_id as 'EmployeeID'

,RTRIM(empper.last_name) AS 'LastName'
,RTRIM(empper.first_name) AS 'FirstName'
,empper.company as 'Company'
,CONVERT(VARCHAR (10), MAX(empper.hire_date), 101) AS 'HireDate'

,empjob.emp_id AS 'EmpID'
,empjob.company AS 'Company'
,empjob.job_code AS 'JobCode'
,empjob.job_code_id AS 'JobID'

,tbljob.job_title AS 'JobDescription'
,tbljob.category AS 'JobStatus'

FROM empper

inner join empjob ON empper.emp_id = empjob.emp_id
inner join tbljob ON empjob.job_code = tbljob.job_code

WHERE empjob.job_code IN ('2354', '2358', '2360')AND tbljob.category = 'a' AND empjob.job_code_id = 'PRIM'
ORDER BY LastName

 

[ousoonerjoe]

Since you are using the Aggregate MAX, you need to add a GROUP BY statement after the WHERE block.

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[drlex]

I'd consider having a sub-query on empper to bring back the latest hire date and joining that to the main query.

e.g.
Refer to T1.hire_date in the main SELECT and have

INNER JOIN (SELECT emp_id, MAX(hire_date) as hire_date FROM empper GROUP BY emp_id)T1 on empper.emp_id = T1.emp_id

in the JOIN segment

soi là, soi carré

 

[StacyStacy]

Thanks! I found the mistake late yesterday. I did have MAX in the date conversion code. Once I removed it, it worked perfectly.

Thanks!