UNION woes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UNION woes

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2008-11-25 : 11:51:46

At our company, if an employee is terminated (quits or is fired) and returns later, they are assigned a new employee number. The primary key in the employee table is called COUNT.

I'm trying to UNION two tables for a combined report; however, the Test_Results table only stores the Employee information by their name, not their ID number.

Query Analyzer's Parse Query (Ctrl+F5) will accept this routine, but when I try to save it as a query in SQL Server I get, "Error 147: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being agregated is an outer reference.":

Declare @Name varchar(100)
Set @Name = 'Steven Smith'
--CREATE PROCEDURE AcpReport_ByOperator(@Name varchar(100)) AS
Declare @index int
Declare @first varchar(102)
Declare @last varchar(102)
Set @index = CharIndex(' ', @Name)
Set @first = '%'+RTrim(LTrim(SubString(@Name, 1, @index - 1)))+'%'
Set @last = '%'+RTrim(LTrim(SubString(@Name, @index + 1, Len(@Name))))+'%'

SELECT	EI.[FIRSTNAME]+' '+EI.[LASTNAME] AS 'Operator', [System_ID], [Test_Result]
FROM	Final_Check FC LEFT JOIN EmployeeInfo EI ON RTrim(FC.[ID])=RTrim(EI.[ID])
WHERE	(EI.[FIRSTNAME] like @first) and (EI.[LASTNAME] like @last)

UNION

SELECT
	CASE WHEN (
		(SELECT Count(LASTNAME)
		FROM EmployeeInfo
		WHERE TR.[Name]=FIRSTNAME+' '+LASTNAME)>0
	) THEN (
		SELECT FIRSTNAME+' '+LASTNAME AS 'Operator'
		FROM EmployeeInfo
		WHERE (TR.[Name]=FIRSTNAME+' '+LASTNAME) AND ([COUNT]=Max([COUNT]))
--		WHERE (TR.[Name]=FIRSTNAME+' '+LASTNAME)
--		GROUP BY FIRSTNAME+' '+LASTNAME
--		HAVING [COUNT]=Max([COUNT])
	) ELSE
		TR.[ID]
	END AS 'Operator',
	TR.[System_ID], TR.[Test_Result]
FROM	Test_Results TR

I've never used HAVING before, but I tried above in the commented out section. Again, Query Analyzer willt parse this technique, but I can not store the procedure in SQL Server - it gives me "Error 156: Incorrect syntax near the keyword 'GROUP'."

Could someone tell me how to fix this? Maybe suggest some improvements to the code if techniques are bad (I'm a software developer, not a DBA).

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 12:01:18

can you explain what you're trying to do in below case statement with a data sample?


	CASE WHEN (
		(SELECT Count(LASTNAME)
		FROM EmployeeInfo
		WHERE TR.[Name]=FIRSTNAME+' '+LASTNAME)>0
	) THEN (
		SELECT FIRSTNAME+' '+LASTNAME AS 'Operator'
		FROM EmployeeInfo
		WHERE (TR.[Name]=FIRSTNAME+' '+LASTNAME) AND ([COUNT]=Max([COUNT]))
--		WHERE (TR.[Name]=FIRSTNAME+' '+LASTNAME)
--		GROUP BY FIRSTNAME+' '+LASTNAME
--		HAVING [COUNT]=Max([COUNT])
	) ELSE
		TR.[ID]
	END AS 'Operator',
	TR.[System_ID], TR.[Test_Result]
FROM	Test_Results TR

jp2code
Posting Yak Master

175 Posts

Posted - 2008-11-25 : 12:46:42

Avoid Sears Home Improvement

jp2code
Posting Yak Master

175 Posts

Posted - 2008-11-25 : 12:49:26

Further, all tables are horribly designed: Until the COUNT field was added, all fields were char arrays. Now, COUNT is an INT and DATE_TIME is a DateTime. So far, everything else is still an array of chars.

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 13:17:45

wont this do it?

SELECT *
FROM EMployee_Info ei
JOIN Test_Results tr
ON tr.Name=ei.FIRSTNAME +' '+ ei.LASTNAME

jp2code
Posting Yak Master

175 Posts

Posted - 2008-11-25 : 13:30:19

Unfortunately, this will not work.

For employee Ida Bermuda with 2 entries in the Employee table, work would be counted twice or half the time.

Avoid Sears Home Improvement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 13:33:06

quote:
Originally posted by jp2code

Unfortunately, this will not work.

For employee Ida Bermuda with 2 entries in the Employee table, work would be counted twice or half the time.

Avoid Sears Home Improvement

nope take COUNT(DISTINCT Workfield)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-11-26 : 05:52:42

Beware ... you are really going to be screwed when 2 employees of the same name join your organisation ... get onto HR to prevent it happenning

You'll also have difficulty if there are any spelling differences between the 2 tables...best approach is to correct the data model, otherwise you'll be forever swimming against the tide.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources