using ORDER BY CASE with a SELECT UNION

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)

using ORDER BY CASE with a SELECT UNION

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2005-08-21 : 10:02:55

Hmmm! I'm familiar with the ORDER BY CASE... (as per http://www.sqlteam.com/item.asp?ItemID=2209)

but I can't get it to work with my stored procedure that contains a UNION of 2 SELECT statements.

When I try to save the new procedure, I get the error:
ORDER BY items must appear in the select list if the statement contains a UNION operator.

Obviously, the field IS in the select list(s).

Any thoughts?

Thanks and regards
Mark

Kristen
Test

22859 Posts

Posted - 2005-08-21 : 10:09:05

Are you using some sort of TOP in one of the UNIONS (with its own ORDER BY)?

Along that line of thinking:


SELECT *
FROM
(
    SELECT A, B, C
    FROM Table1
    UNION
    SELECT X, Y, Z
    FROM Table2
) X
ORDER BY A, B, C

Kristen

mark1504
Posting Yak Master

103 Posts

Posted - 2005-08-21 : 14:17:22

Thanks Kristen

You're right. Putting the 'unionized' selects within the

SELECT ( ... ) x ORDER BY

did the trick!

thanks and regards
Mark

VladRUS.ca
Starting Member

23 Posts

Posted - 2005-08-22 : 13:39:06

OR Like this:

SELECT A, B, C
FROM Table1
UNION
SELECT X, Y, Z
FROM Table2
ORDER BY 1,2,3

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-22 : 13:46:43

quote:
Originally posted by VladRUS.ca

OR Like this:

SELECT A, B, C
FROM Table1
UNION
SELECT X, Y, Z
FROM Table2
ORDER BY 1,2,3

Using ordinals in the ORDER BY is not recommended. It is always best to refer to the column names in case the ordinal position changes in the query.

Tara

mark1504
Posting Yak Master

103 Posts

Posted - 2005-08-26 : 06:57:08

quote:
Originally posted by VladRUS.ca

OR Like this:

SELECT A, B, C
FROM Table1
UNION
SELECT X, Y, Z
FROM Table2
ORDER BY 1,2,3

Aside from the already mentioned point about ordinals in the order by, the problem is when using ORDER BY CASE etc the usual query structure you offered is inadequate (see the red error in my original message). Therefore Kristen's method was the perfect answer to my problem.

CASE CLOSED

Regards Mark

rramya
Starting Member

2 Posts

Posted - 2007-09-27 : 07:56:16

Hi,

i am facing similar problem, i have used union with order by, but the problem is when i use order by case when @reverse = 0 then followUpDate else followUpDate desc end it is giving error that

Incorrect syntax near the keyword 'desc'.



DECLARE @queryString varchar(8000),
@userPosition varchar(255),
@seniorPosition varchar(255),
@usePB varchar(255),
@escalteToSeniorCollOff varchar(255),
@orderBy varchar(20),
@reverse SMALLINT,
@userId int,
SET @userId = 8
SET @reverse = 0
IF(@reverse = 0 OR @reverse is NULL)
BEGIN
	set @orderBy = 'followUpDate DESC'
END
ELSE
BEGIN
	set @orderBy = 'followUpDate'
END 
select * from (select  m.memNo, m.memBranch, m.cifId, cs.csId, c.contactId,followUpDate, contactStatus, comment,
dateUpdated, prodGroup, prodSub, prodRowNo, prodType, m.memCollOfficer, userName, userSurName 
from member m join memberDetails me on m.cifId = me.cifId, contacts c,contactStatus cs join contactStates cse 
on cse.csStatusId = cs.contactStatus, users u where 
((memPb in (SELECT userFSRId FROM users WHERE (userForwardTo =  cast(@userId as varchar)   
AND userFSRId !=  cast ( @userId as  varchar)  ) OR (userFSRId = cast(@userId as varchar)   
AND userForwardTo is null)) and  isnull (m.memCollOfficer, 0)  != cast(@userId as varchar) 
and seniorEscalation = 0) or (memCollOfficer in (SELECT userFSRId FROM users WHERE 
(userForwardTo = cast(@userId as varchar)  AND userFSRId != cast(@userId as varchar)  ) 
OR (userFSRId = cast(@userId as varchar)   AND userForwardTo is null)) and seniorEscalation = 1)) and
m.cifId = c.cifId and userFSRId = modifiedBy and c.contactId = cs.contactId and cse.isOpen = 1 and
statusCnt = (select max(statusCnt)from contactStatus where contactId =c.contactId) 
AND (followUpDate BETWEEN  @startdate  AND   DATEADD(day, 1, @enddate) ) 
UNION 
select m.memNo, m.memBranch, m.cifId,cs.csId,c.contactId,followUpDate,contactStatus,comment,dateUpdated,prodGroup,prodSub, prodRowNo, prodType,m.memCollOfficer,'Automated' AS userName, 'Automated' AS userSurName from member m join memberDetails me on m.cifId = me.cifId, contacts c,contactStatus cs join contactStates cse on cse.csStatusId = cs.contactStatus where 
((memPb in (SELECT userFSRId FROM users WHERE (userForwardTo =  CONVERT ( varchar(10) , @userId )   AND userFSRId !=  CONVERT ( varchar(10) , @userId ) ) OR (userFSRId = CONVERT ( varchar(10) , @userId )  AND userForwardTo is null)) and  isnull (m.memCollOfficer, 0)  != CONVERT ( varchar(10) , @userId )  and seniorEscalation = 0) or
(memCollOfficer in (SELECT userFSRId FROM users WHERE (userForwardTo = CONVERT ( varchar(10) , @userId )  AND userFSRId != CONVERT ( varchar(10) , @userId )  ) OR (userFSRId = CONVERT ( varchar(10) , @userId )  AND userForwardTo is null)) and seniorEscalation = 1)) and
m.cifId = c.cifId and
modifiedBy is null and
c.contactId = cs.contactId and
cse.isOpen = 1 and
statusCnt = (select max(statusCnt)from contactStatus where contactId =c.contactId) AND (followUpDate BETWEEN  CAST(@startdate as nvarchar ) AND  DATEADD(day, 1, CONVERT(char(180), @startdate )))) x
ORDER BY CASE WHEN (@reverse = 0 OR @reverse is NULL) THEN followUpDate  else followUpDate desc  end

Please hepl me it is very urgent i am using sql2000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 08:28:09

This is the only ORDER BY you need in the query above

ORDER BY	CASE
			WHEN @Reverse = 0 OR @Reverse IS NULL THEN DATEDIFF(DAY, 0, FollowUpDate)
			ELSE DATEDIFF(DAY, FollowUpDate, 0)
		END

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 08:34:08

Do you think the DATEDIFF is more CPU than, say:


ORDER BY
	CASE
		WHEN @Reverse = 0 OR @Reverse IS NULL THEN FollowUpDate ELSE NULL END
	END,
	CASE
		WHEN @Reverse = 0 OR @Reverse IS NULL THEN NULL ELSE FollowUpDate END DESC
	END

(I have no idea! hence the question)

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 08:43:44

That has to be the wrost written code I ever encountered.

1) CROSS JOINS deluxe!
2) != instead of <>
3) No feeling for operator presedence
4) Unnecessary datatype conversions

rramya, truthfully, are you having performance trouble with this query above?

E 12°55'05.25"
N 56°04'39.16"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-09-27 : 09:13:45

Peter, you missed the SELECT * FROM

select * from (select  m.memNo,

Which probably isn't even needed

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 09:19:57

And @startdate / @enddate undefined.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 09:28:41

Here is your query with some modications, and in human readable form.

SELECT		m.memNo,
		m.memBranch,
		m.cifId,
		cs.csId,
		c.contactId,
		{table alias here}.followUpDate,
		{table alias here}.contactStatus,
		{table alias here}.comment,
		{table alias here}.dateUpdated,
		{table alias here}.prodGroup,
		{table alias here}.prodSub,
		{table alias here}.prodRowNo,
		{table alias here}.prodType,
		m.memCollOfficer,
		{table alias here}.userName,
		{table alias here}.userSurName 
FROM		member as m
INNER JOIN	memberDetails as me on me.cifId = m.cifId
INNER JOIN	contacts as c ON c.cifId = m.cifId
INNER JOIN	contactStatus as cs ON cs.contactId = c.contactId
INNER JOIN	contactStates cse on cse.csStatusId = cs.contactStatus
CROSS JOIN	users as u
WHERE		(
			(
				{table alias here}.memPb IN (SELECT y.userFSRId FROM users AS y WHERE (y.userForwardTo = @UserID AND y.UserFSRId <> @UserID) OR (y.userFSRId = @UserID AND y.userForwardTo IS NULL))
				AND m.memCollOfficer <> @UserID
				AND {table alias here}.SeniorEscalation = 0
			)
			OR
			(
				m.memCollOfficer in (SELECT y.userFSRId FROM users as y WHERE (y.userForwardTo = @userId AND y.userFSRId <> @userId) OR (y.userFSRId = @userId AND y.userForwardTo IS NULL))
				and {table alias here}.SeniorEscalation = 1
			)
		)
		AND u.userFSRId = {table alias here}.modifiedBy
		and cse.isOpen = 1
		and {table alias here}.statusCnt = (select max(x.statusCnt)from contactStatus as x where x.contactId =c.contactId) 
		AND {table alias here}.FollowUpDate >= DATEADD(DAY, DATEDIFF(DAY, 0, @Startdate), 0)
		AND {table alias here}.FollowUpDate < DATEADD(DAY, DATEDIFF(DAY, 0, @EndDate), 1)

UNION

select		m.memNo,
		m.memBranch,
		m.cifId,
		cs.csId,
		c.contactId,
		followUpDate,
		contactStatus,
		comment,
		dateUpdated,
		prodGroup,
		prodSub,
		prodRowNo,
		prodType,
		m.memCollOfficer,
		'Automated' AS userName,
		'Automated' AS userSurName
from		member as m
inner join	memberDetails as me on me.cifId = m.cifId
inner join	contacts as c on c.cifId = m.cifId
inner join	contactStatus as cs ON cs.contactId = c.contactId
inner join	contactStates as cse on cse.csStatusId = cs.contactStatus
where		(
			(
				{table alias here}.memPb in (SELECT y.userFSRId FROM users as y WHERE (y.userForwardTo = @userId  AND y.userFSRId <>  @userId ) OR (y.userFSRId = @userId  AND y.userForwardTo IS NULL))
				and m.memCollOfficer <> @userId 
				and {table alias here}.seniorEscalation = 0
			)
		or
			(
				m.memCollOfficer in (SELECT y.userFSRId FROM users as y WHERE (y.userForwardTo = @userId  AND y.userFSRId <> @userId ) OR (y.userFSRId = @userId AND y.userForwardTo IS NULL))
				and {table alias here}.seniorEscalation = 1
			)
		)
		and {table alias here}.modifiedBy is null
		and cse.isOpen = 1
		and {table alias here}.statusCnt = (select max(x.statusCnt)from contactStatus as x where x.contactId =c.contactId)
		AND {table alias here}.FollowUpDate >= DATEADD(DAY, DATEDIFF(DAY, 0, @Startdate), 0)
		AND {table alias here}.FollowUpDate < DATEADD(DAY, DATEDIFF(DAY, 0, @StartDate), 1)

ORDER BY	CASE
			WHEN @Reverse = 0 OR @Reverse IS NULL THEN DATEDIFF(DAY, 0, FollowUpDate)
			ELSE DATEDIFF(DAY, FollowUpDate, 0)
		END

E 12°55'05.25"
N 56°04'39.16"

rramya
Starting Member

2 Posts

Posted - 2007-09-27 : 09:50:09

hey,
I used it with some changes , it is working thanks a lot......

Ramya

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 09:59:39

Given the huge similarity between the two UNIONs I would pull the relevant data into a Temp table first, and then UNION the appropriate, and different, stuff as a second step.

Kristen

shijobaby
Starting Member

44 Posts

Posted - 2009-08-19 : 05:41:55

Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query

http://sqlerror104.blogspot.com/2009/08/order-by-items-must-appear-in-select_19.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources