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 2005 Forums
 Transact-SQL (2005)
 Dynamic ORDER BY and UNION

Author  Topic 

ias0nas
Starting Member

36 Posts
Posted - 2008-05-13 : 04:31:16
Hello,

I am trying to use dynamic ORDER BY with UNION in a stored procedure but I keep getting this error message:

ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

The ORDER BY works fine without the UNION, and the UNION works fine without the dynamic ORDER BY (just putting an ORDER BY works fine)

Here is the whole query in case this helps, thank you

SELECT
leave_id, leave_type, annualLeave.from_date,
convert(nvarchar(10), annualLeave.request_date, 103) as dated,
convert(nvarchar(10), annualLeave.from_date, 103) as from_date,
convert(nvarchar(10), annualLeave.to_date, 103) as to_date,
annualLeave.canceled,
annualLeave.working_days,
staff.staff_name,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem'
ELSE ''
END AS pendingStyle,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
ELSE ''
END AS historyStyle
FROM annualLeave
INNER JOIN staff ON annualLeave.staff = staff.staff_id
WHERE staff.department like @Department
and (@staffID is Null or annualLeave.staff = @StaffID)
and (@Canceled is Null or annualLeave.canceled = @Canceled)
and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null)
and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null)
and (@StartingDate is Null or annualLeave.from_date > @Startingdate)
and (@IsPastLeave is Null or annualLeave.from_date < @IsPastLeave)
and (@EndingDate is Null or annualLeave.to_date <= @EndingDate)

UNION

SELECT
leave_id, leave_type, annualLeave.from_date,
convert(nvarchar(10), annualLeave.request_date, 103) as dated,
convert(nvarchar(10), annualLeave.from_date, 103) as from_date,
convert(nvarchar(10), annualLeave.to_date, 103) as to_date,
annualLeave.canceled,
annualLeave.working_days,
staff.staff_name,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem'
ELSE ''
END AS pendingStyle,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
ELSE ''
END AS historyStyle
FROM annualLeave
INNER JOIN staff ON annualLeave.staff = staff.staff_id
WHERE staff.department like @Department
and (@staffID is Null or annualLeave.staff = @StaffID)
and (@Canceled is Null or annualLeave.canceled = @Canceled)
and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null)
and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null)
and annualLeave.leave_type like 'Leave in lieu'
ORDER BY
CASE @OrderDir
WHEN 'ASC' THEN annualLeave.from_date
END
ASC,
CASE @OrderDir
WHEN 'DESC' THEN annualLeave.from_date
END
DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-05-13 : 04:44:20
try like this:-

SELECT *
FROM
(SELECT
leave_id, leave_type, annualLeave.from_date,
convert(nvarchar(10), annualLeave.request_date, 103) as dated,
convert(nvarchar(10), annualLeave.from_date, 103) as from_date,
convert(nvarchar(10), annualLeave.to_date, 103) as to_date,
annualLeave.canceled,
annualLeave.working_days,
staff.staff_name,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem'
ELSE ''
END AS pendingStyle,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
ELSE ''
END AS historyStyle
FROM annualLeave
INNER JOIN staff ON annualLeave.staff = staff.staff_id
WHERE staff.department like @Department
and (@staffID is Null or annualLeave.staff = @StaffID)
and (@Canceled is Null or annualLeave.canceled = @Canceled)
and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null)
and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null)
and (@StartingDate is Null or annualLeave.from_date > @Startingdate)
and (@IsPastLeave is Null or annualLeave.from_date < @IsPastLeave)
and (@EndingDate is Null or annualLeave.to_date <= @EndingDate)

UNION

SELECT
leave_id, leave_type, annualLeave.from_date,
convert(nvarchar(10), annualLeave.request_date, 103) as dated,
convert(nvarchar(10), annualLeave.from_date, 103) as from_date,
convert(nvarchar(10), annualLeave.to_date, 103) as to_date,
annualLeave.canceled,
annualLeave.working_days,
staff.staff_name,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem'
ELSE ''
END AS pendingStyle,
Case WHEN annualLeave.canceled = 'True' THEN 'canceledPO'
ELSE ''
END AS historyStyle
FROM annualLeave
INNER JOIN staff ON annualLeave.staff = staff.staff_id
WHERE staff.department like @Department
and (@staffID is Null or annualLeave.staff = @StaffID)
and (@Canceled is Null or annualLeave.canceled = @Canceled)
and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null)
and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null)
and annualLeave.leave_type like 'Leave in lieu')t
ORDER BY
CASE @OrderDir
WHEN 'ASC' THEN t.from_date
END
ASC,
CASE @OrderDir
WHEN 'DESC' THEN t.from_date
END
DESC
Go to Top of Page

ias0nas
Starting Member

36 Posts
Posted - 2008-05-13 : 04:51:21
Nice one, thanks
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-05-13 : 05:34:48
Why union when 85% of code is the same?
SELECT		leave_id,
		leave_type,
		annualLeave.from_date,
		convert(nvarchar(10), annualLeave.request_date, 103) as dated, 
		convert(nvarchar(10), annualLeave.from_date, 103) as from_date, 
		convert(nvarchar(10), annualLeave.to_date, 103) as to_date,
		annualLeave.canceled, 
		annualLeave.working_days,
		staff.staff_name,
		Case
			WHEN annualLeave.canceled = 'True' THEN 'canceledPO' 
			WHEN annualLeave.authorized_mng is not NUll AND annualLeave.authorized_hr is not Null THEN 'greenItem' 
			ELSE '' 
		END AS pendingStyle,
		Case
			WHEN annualLeave.canceled = 'True' THEN 'canceledPO' 
			ELSE '' 
		END AS historyStyle
FROM		annualLeave
INNER JOIN	staff ON annualLeave.staff = staff.staff_id
WHERE		staff.department like @Department
		and (@staffID is Null or annualLeave.staff = @StaffID)
		and (@Canceled is Null or annualLeave.canceled = @Canceled)
		and (@AuthorizedMng is Null or annualLeave.authorized_mng is Null)
		and (@AuthorizedHR is Null or annualLeave.authorized_hr is Null)
		AND	(
				(@StartingDate is Null or annualLeave.from_date > @Startingdate)
				and (@IsPastLeave is Null or annualLeave.from_date < @IsPastLeave)
				and (@EndingDate is Null or annualLeave.to_date <= @EndingDate)
			or
				annualLeave.leave_type like 'Leave in lieu'
			)
ORDER BY	CASE @OrderDir
			WHEN 'ASC' THEN annualLeave.from_date
		END ASC,
		CASE @OrderDir
			WHEN 'DESC' THEN annualLeave.from_date
		END DESC


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site