[thelist] SQL UNION choking on ORDER BY clause
JCanfield at magisnetworks.com
JCanfield at magisnetworks.com
Wed May 15 21:04:01 CDT 2002
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
--------------------------------------------------
More information about the thelist
mailing list