[thelist] SQL UNION choking on ORDER BY clause

[JCanfield at magisnetworks.com]

Trying to create a stored procedure as follows, but the groovy 'Check
Syntax' button tells me that there's an error near 'ORDER'

And, yes, I'm positive there must be a cleaner way to do this. I'm wide open
for suggestions on general efficiency here. And if it's necessary to
describe the logic in order to pinpoint the error I'll do that, but this is
long enough without it.

Tenks.

joel

------------------------------------------------
-- lists all requests pending action by an EmpID
-- receives one parameter, an EmpID int
CREATE PROCEDURE GetPendingBySupervisorEmpID2
(
	@SupervisorEmpID int
)
AS
-- uses SupervisorEmpID as the critical field
-- all requests of type computer plus all from IT department
IF @SupervisorEmpID = 45
BEGIN
	(
		SELECT r.po_ID, p.e_ID, r.por_Sent, r.por_Comments,
r.por_Result, p.po_Type
		FROM po_Routing AS r
		JOIN
		po AS p
		ON
		p.po_ID  = r.po_ID
		WHERE por_Result = 'pending' AND po_Type = 'computer'
		AND r.e_id = 45
		AND r.por_Sent > GETDATE() - 90
		AND r.po_ID NOT IN
		-- ignores all po_IDs with these as the status since they
are not pending action
		(
			SELECT DISTINCT po_ID
			FROM po_Routing
			WHERE por_Result = 'approved' OR por_Result =
'voided' OR por_Result = 'denied'
		)
	)
	UNION
	(
		SELECT r.po_ID, r.e_ID, r.por_Sent, r.por_Comments,
r.por_Result, e.SupervisorEmpID
		FROM po_Routing AS r
		JOIN
		Employees AS e
		ON
		r.e_ID = e.EmpID
		WHERE por_Result = 'pending'
		-- limits query to last 90 days
		AND por_Sent > GETDATE() - 90
		AND po_ID NOT IN
		-- ignores all po_IDs with these as the status since they
are not pending action
		(
			SELECT DISTINCT po_ID
			FROM po_Routing
			WHERE por_Result = 'approved' OR por_Result =
'voided' OR por_Result = 'denied'
		)
		AND e.SupervisorEmpID = @SupervisorEmpID
	)
	ORDER BY r.po_ID DESC
END
ELSE
-- uses SupervisorEmpID as the critical field
-- all requests of type test plus all from lab department
IF @SupervisorEmpID = 36
BEGIN
	(
	SELECT r.po_ID, p.e_ID, r.por_Sent, r.por_Comments, r.por_Result,
p.po_Type
	FROM po_Routing AS r
	JOIN
	po AS p
	ON
	p.po_ID  = r.po_ID
	WHERE por_Result = 'pending' AND po_Type = 'test'
	AND r.e_id = 36
	AND r.por_Sent > GETDATE() - 90
	AND r.po_ID NOT IN
	-- ignores all po_IDs with these as the status since they are not
pending action
	(
		SELECT DISTINCT po_ID
		FROM po_Routing
		WHERE por_Result = 'approved' OR por_Result = 'voided' OR
por_Result = 'denied'
	)
	UNION
	(
		SELECT r.po_ID, r.e_ID, r.por_Sent, r.por_Comments,
r.por_Result, e.SupervisorEmpID
		FROM po_Routing AS r
		JOIN
		Employees AS e
		ON
		r.e_ID = e.EmpID
		WHERE por_Result = 'pending'
		-- limits query to last 90 days
		AND por_Sent > GETDATE() - 90
		AND po_ID NOT IN
		-- ignores all po_IDs with these as the status since they
are not pending action
		(
			SELECT DISTINCT po_ID
			FROM po_Routing
			WHERE por_Result = 'approved' OR por_Result =
'voided' OR por_Result = 'denied'
		)
		AND e.SupervisorEmpID = @SupervisorEmpID
	)
	ORDER BY r.po_ID DESC
END
ELSE
-- uses SupervisorEmpID as the critical field
-- all requests submitted are grouped by the supervisor of the submitter
BEGIN
	SELECT r.po_ID, r.e_ID, r.por_Sent, r.por_Comments, r.por_Result,
e.SupervisorEmpID
	FROM po_Routing AS r
	JOIN
	Employees AS e
	ON
	r.e_ID = e.EmpID
	WHERE por_Result = 'pending'
	-- limits query to last 90 days
	AND por_Sent > GETDATE() - 90
	AND po_ID NOT IN
	-- ignores all po_IDs with these as the status since they are not
pending action
	(
		SELECT DISTINCT po_ID
		FROM po_Routing
		WHERE por_Result = 'approved' OR por_Result = 'voided' OR
por_Result = 'denied'
	)
	AND e.SupervisorEmpID = @SupervisorEmpID
	ORDER BY r.po_ID DESC
END
--------------------------------------------------