new stored procedure won't save

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)

new stored procedure won't save

Author

Topic

jp2code
Posting Yak Master

175 Posts

Posted - 2008-12-11 : 13:31:08

I'm trying to create a new stored procedure in SQL 2000.

The syntax seems ok to me, but I get this message box every time I attempt to save:

Title: Microsoft SQL-DMO (ODBC SQLState: 4200)
Message: Error 104: ORDER BY items must appear in the select list if the statement contains a UNION operator.

Could someone tell me how to fix this?

I started by copying an existing query, modified it to only return the Operator field. This worked but returned duplicate operator names, so I modified the "Test_Results" query (last one), and now I can not save.

Here is my SQL procedure (not saved):

CREATE PROCEDURE AcpReport_OperatorByDates(@DateStart DateTime, @DateEnd DateTime) AS
/* Created by Joe Pool for the new ACP Reporter - Returns records ordered by Date_Time */
SELECT DISTINCT EI.[FIRSTNAME]+' '+EI.[LASTNAME]+' ('+AP.[OP_ID]+')' AS Operator
FROM		ACP_Parts AP LEFT JOIN EmployeeInfo EI ON RTrim(AP.[OP_ID])=RTrim(EI.[NUM])
WHERE	(@DateStart<[Date_Time]) AND ([Date_Time]<@DateEnd)

UNION

SELECT DISTINCT EI.[FIRSTNAME]+' '+EI.[LASTNAME]+' ('+FC.[OP_ID]+')' AS Operator
FROM		Final_Check FC LEFT JOIN EmployeeInfo EI ON RTrim(FC.[OP_ID])=RTrim(EI.[NUM])
WHERE 	(@DateStart<[Date_Time]) AND ([Date_Time]<@DateEnd)

UNION

SELECT DISTINCT EI.[FIRSTNAME]+' '+EI.[LASTNAME]+' ('+BD.[OP_ID]+')' AS Operator
FROM		Box_Data BD INNER JOIN Box_Contents BC ON BD.[Box_Number]=BC.[Box_Number] LEFT JOIN EmployeeInfo EI ON RTrim(BD.[OP_ID])=RTrim(EI.[NUM])
WHERE	(@DateStart<[Date_Time]) AND ([Date_Time]<@DateEnd)

UNION

SELECT DISTINCT CASE WHEN ((SELECT Count(FIRSTNAME) FROM EmployeeInfo WHERE (Replace(RTrim(TR.OP_ID), '  ', ' ')=(RTrim(FirstName)+' '+RTrim(LastName))))>0) THEN (SELECT TOP 1 FIRSTNAME+' '+LASTNAME+' ('+NUM+')' AS Operator FROM EmployeeInfo WHERE (Replace(RTrim(TR.OP_ID), '  ', ' ')=(RTrim(FirstName)+' '+RTrim(LastName))) ORDER BY [COUNT] DESC) ELSE TR.[OP_ID] END AS Operator
FROM		Test_Results TR
WHERE	(@DateStart<[Date_Time]) AND ([Date_Time]<@DateEnd)

ORDER BY Operator
GO

Avoid Sears Home Improvement

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-11 : 13:34:26

[code]
CREATE PROCEDURE AcpReport_OperatorByDates(@DateStart DateTime, @DateEnd DateTime) AS
/* Created by Joe Pool for the new ACP Reporter - Returns records ordered by Date_Time */

SELECT * FROM (
SELECT DISTINCT EI.[FIRSTNAME]+' '+EI.[LASTNAME]+' ('+AP.[OP_ID]+')' AS Operator
FROM ACP_Parts AP LEFT JOIN EmployeeInfo EI ON RTrim(AP.[OP_ID])=RTrim(EI.[NUM])
WHERE (@DateStart<[Date_Time]) AND ([Date_Time]<@DateEnd)

UNION

SELECT DISTINCT EI.[FIRSTNAME]+' '+EI.[LASTNAME]+' ('+FC.[OP_ID]+')' AS Operator
FROM Final_Check FC LEFT JOIN EmployeeInfo EI ON RTrim(FC.[OP_ID])=RTrim(EI.[NUM])
WHERE (@DateStart<[Date_Time]) AND ([Date_Time]<@DateEnd)

UNION

SELECT DISTINCT EI.[FIRSTNAME]+' '+EI.[LASTNAME]+' ('+BD.[OP_ID]+')' AS Operator
FROM Box_Data BD INNER JOIN Box_Contents BC ON BD.[Box_Number]=BC.[Box_Number] LEFT JOIN EmployeeInfo EI ON RTrim(BD.[OP_ID])=RTrim(EI.[NUM])
WHERE (@DateStart<[Date_Time]) AND ([Date_Time]<@DateEnd)

UNION

SELECT DISTINCT CASE WHEN ((SELECT Count(FIRSTNAME) FROM EmployeeInfo WHERE (Replace(RTrim(TR.OP_ID), ' ', ' ')=(RTrim(FirstName)+' '+RTrim(LastName))))>0) THEN (SELECT TOP 1 FIRSTNAME+' '+LASTNAME+' ('+NUM+')' AS Operator FROM EmployeeInfo WHERE (Replace(RTrim(TR.OP_ID), ' ', ' ')=(RTrim(FirstName)+' '+RTrim(LastName))) ORDER BY [COUNT] DESC) ELSE TR.[OP_ID] END AS Operator
FROM Test_Results TR
WHERE (@DateStart<[Date_Time]) AND ([Date_Time]<@DateEnd)
) AS XXX
ORDER BY Operator
GO
[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jp2code
Posting Yak Master

175 Posts

Posted - 2008-12-11 : 13:37:43

OMG! Sweet, sweet, sweet!

Thank you, Brett.

Avoid Sears Home Improvement

X002548
Not Just a Number

15586 Posts

Posted - 2008-12-11 : 13:41:30

And dude...I would sue the living heck out of Sears

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources