order by ITEMS MUST APPEAR IN THE SELECT LIST IF T

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)

order by ITEMS MUST APPEAR IN THE SELECT LIST IF T

Author

Topic

drolfson
Starting Member

3 Posts

Posted - 2004-11-09 : 10:25:46

Hi;

I am trying to get a dynamic ORDER BY statement to function but I am getting the following error:

order by ITEMS MUST APPEAR IN THE SELECT LIST IF THE STATEMENT CONTAINS A union operator

which is driving me nuts because all of the items in the case for the ORDER BY are in both of the select statements in the Union.

*****************
Thanx,

Don

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-09 : 10:29:49

Do you think maybe if we saw your code it would be a little easier for us to help you?

- Jeff

drolfson
Starting Member

3 Posts

Posted - 2004-11-09 : 10:45:19

Sorry, I knew I forgot something! Phone Rang.

Here it is:

ALTER PROCEDURE usp_PDGetViewableBundles
(
@ContactID int,
@SearchType varchar(25) = null,
@SearchStr varchar(25) = null,
@ascend as varchar(5) = 'asc'

)
AS

Declare @Parent_ID as int,
@AccountID as int;

Select @Parent_ID = DOA.Parent_ID, @AccountID = DOA.AccountID
From PDOrders..Contacts DOC
Inner Join PDOrders..Account DOA on DOC.AccountID = DOA.AccountID
Where (DOC.ContactID = @ContactID);

If (@Parent_ID Is Null) -- the contact belongs to a Parent Account
Begin
Select CB.Basket_ID, CB.Bundle_Name, CB.Comments, CB.Order_Total, CB.Bundle_Type_ID, CB.BundleSequence, CB.Bus_Entity_ID, CB.activeto, CB.email, 'Public' as 'PublicPrivate', bt.bundle_type_name, CT.First_Name+' '+CT.Last_Name as 'Creator_Name'
From t_Basket CB
Inner Join PDOrders..Contacts CT ON ((CT.email = CB.email) and (CT.accountid = CB.bus_entity_id))
LEFT OUTER JOIN t_bundle_types bt ON (bt.bundle_type_id = CB.bundle_type_id)
Where ((CB.Bus_Entity_ID in (
Select DOA.AccountID
From PDOrders..Account DOA
Where DOA.Parent_ID = @AccountID )
Or (CB.Bus_Entity_ID = @AccountID))
and (CB.type = 'STD')
and ((CB.Status is Null) or (CB.Status <> 'Delete'))
and (CB.active = 1)
and (CB.IsPublic = 1)
and ((CB.ActiveTo IS NULL) OR ((GetDate() <= CB.ActiveTo) and (GetDate() >= CB.ActiveFrom))
and (CT.Active = 'Y' )
AND
(
(@SearchType NOT IN ('Name','Creator','CreatorLName','CreatorFName', 'Category'))
OR
(@SearchType = 'Name' AND cb.bundle_name LIKE '%' + @SearchStr + '%' )
OR
(@SearchType = 'Creator' AND CB.email LIKE '%' + @SearchStr + '%' )
OR
(@SearchType = 'Category' AND bt.bundle_type_name LIKE '%' + @SearchStr + '%' )
)
))
Union All -- Union All Public bundles from both child and parent with Private bundles of the account specified...
Select CB.Basket_ID, CB.Bundle_Name, CB.Comments, CB.Order_Total, CB.Bundle_Type_ID, CB.BundleSequence, CB.Bus_Entity_ID, CB.activeto, CB.email, 'Private' as 'PublicPrivate', bt.bundle_type_name, CT.First_Name+' '+CT.Last_Name as 'Creator_Name'
From t_Basket CB
Inner Join PDOrders..Contacts CT ON ((CT.email = CB.email) and (CT.accountid = CB.bus_entity_id))
LEFT OUTER JOIN t_bundle_types bt ON (bt.bundle_type_id = CB.bundle_type_id)
Where ((CB.Bus_Entity_ID = @AccountID)
and (CB.type = 'STD')
and ((CB.Status is Null) or (CB.Status <> 'Delete'))
and (CB.active = 1)
and (CB.IsPublic = 0)
and ((CB.ActiveTo IS NULL) OR ((GetDate() <= CB.ActiveTo) and (GetDate() >= CB.ActiveFrom))
and (CT.Active = 'Y' )
AND
(
(@SearchType NOT IN ('Name','Creator','CreatorLName','CreatorFName', 'Category'))
OR
(@SearchType = 'Name' AND cb.bundle_name LIKE '%' + @SearchStr + '%' )
OR
(@SearchType = 'Creator' AND CB.email LIKE '%' + @SearchStr + '%' )
OR
(@SearchType = 'Category' AND bt.bundle_type_name LIKE '%' + @SearchStr + '%' )
)
))
--THIS IS WHAT I AM ADDING********
Order By CASE WHEN (@SearchType = 'Name' and @ascend = 'asc') THEN CB.Bundle_Name
WHEN (@SearchType = 'Creator' and @ascend = 'asc') THEN CB.email
WHEN (@SearchType = 'Category' and @ascend = 'asc') THEN bt.bundle_type_name
ELSE CB.Bundle_Name
END ASC

End;
Else -- the contact belongs to a Child Account
Begin
Select CB.Basket_ID, CB.Bundle_Name, CB.Comments, CB.Order_Total, CB.Bundle_Type_ID, CB.BundleSequence, CB.Bus_Entity_ID, CB.activeto, CB.email, 'Public' as 'PublicPrivate', bt.bundle_type_name, CT.First_Name+' '+CT.Last_Name as 'Creator_Name'
From t_Basket CB
Inner Join PDOrders..Contacts CT ON ((CT.email = CB.email) and (CT.accountid = CB.bus_entity_id))
LEFT OUTER JOIN t_bundle_types bt ON (bt.bundle_type_id = CB.bundle_type_id)
Where ((CB.Bus_Entity_ID in (
Select DOA.AccountID
From PDOrders..Account DOA
Where DOA.Parent_ID = @Parent_ID)
Or (CB.Bus_Entity_ID = @Parent_ID))
and (CB.type = 'STD')
and ((CB.Status is Null) or (CB.Status <> 'Delete'))
and (CB.active = 1)
and (CB.IsPublic = 1)
and ((CB.ActiveTo IS NULL) OR ((GetDate() <= CB.ActiveTo) and (GetDate() >= CB.ActiveFrom))
and (CT.Active = 'Y' )
AND
(
(@SearchType NOT IN ('Name','Creator','CreatorLName','CreatorFName', 'Category'))
OR
(@SearchType = 'Name' AND cb.bundle_name LIKE '%' + @SearchStr + '%' )
OR
(@SearchType = 'Creator' AND CB.email LIKE '%' + @SearchStr + '%' )
OR
(@SearchType = 'Category' AND bt.bundle_type_name LIKE '%' + @SearchStr + '%' )
)
))
Union -- Union child Private bundles...
Select CB.Basket_ID, CB.Bundle_Name, CB.Comments, CB.Order_Total, CB.Bundle_Type_ID, CB.BundleSequence, CB.Bus_Entity_ID, CB.activeto, CB.email, 'Private' as 'PublicPrivate', bt.bundle_type_name, CT.First_Name+' '+CT.Last_Name as 'Creator_Name'
From t_Basket CB
Inner Join PDOrders..Contacts CT ON ((CT.email = CB.email) and (CT.accountid = CB.bus_entity_id))
LEFT OUTER JOIN t_bundle_types bt ON (bt.bundle_type_id = CB.bundle_type_id)
Where ((CB.Bus_Entity_ID = @AccountID)
and (CB.type = 'STD')
and ((CB.Status is Null) or (CB.Status <> 'Delete'))
and (CB.active = 1)
and (CB.IsPublic = 0)
and ((CB.ActiveTo IS NULL) OR ((GetDate() <= CB.ActiveTo) and (GetDate() >= CB.ActiveFrom))
and (CT.Active = 'Y' )
AND
(
(@SearchType NOT IN ('Name','Creator','CreatorLName','CreatorFName', 'Category'))
OR
(@SearchType = 'Name' AND cb.bundle_name LIKE '%' + @SearchStr + '%' )
OR
(@SearchType = 'Creator' AND CB.email LIKE '%' + @SearchStr + '%' )
OR
(@SearchType = 'Category' AND bt.bundle_type_name LIKE '%' + @SearchStr + '%' )
)
))
Union -- Union Private bundles from Parent that are assigned to the Child...
Select CB.Basket_ID, CB.Bundle_Name, CB.Comments, CB.Order_Total, CB.Bundle_Type_ID, CB.BundleSequence, CB.Bus_Entity_ID, CB.activeto, CB.email, 'Assigned' as 'PublicPrivate', bt.bundle_type_name, CT.First_Name+' '+CT.Last_Name as 'Creator_Name'
From t_Basket CB
Inner join BundleAssignment BA on (BA.BundleAssignmentID = CB.BundleAssignmentID)
Inner Join PDOrders..Contacts CT ON ((CT.email = CB.email) and (CT.accountid = CB.bus_entity_id))
LEFT OUTER JOIN t_bundle_types bt ON (bt.bundle_type_id = CB.bundle_type_id)
Where (((BA.AccountID = @AccountID) or (BA.ContactID = @ContactID))
and (CB.type = 'STD')
and ((CB.Status is Null) or (CB.Status <> 'Delete'))
and (CB.active = 1)
and (CB.IsPublic = 0)
and ((CB.ActiveTo IS NULL) OR ((GetDate() <= CB.ActiveTo) and (GetDate() >= CB.ActiveFrom))
and (CT.Active = 'Y' )
AND
(
(@SearchType NOT IN ('Name','Creator','CreatorLName','CreatorFName', 'Category'))
OR
(@SearchType = 'Name' AND cb.bundle_name LIKE '%' + @SearchStr + '%' )
OR
(@SearchType = 'Creator' AND CB.email LIKE '%' + @SearchStr + '%' )
OR
(@SearchType = 'Category' AND bt.bundle_type_name LIKE '%' + @SearchStr + '%' )
)
))
Order By CB.Bundle_Name;
End;

Return @@rowcount;
GO

*****************
Thanx,

Don

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-09 : 11:37:38

This'll give you a clue
create table #a (i int)
select i from #a
union
select i from #a
order by case when 1=1 then i else i end

basically although CB.Bundle_Name, ... appear in th eselect list
CASE WHEN (@SearchType = 'Name' and @ascend = 'asc') THEN CB.Bundle_Name
WHEN (@SearchType = 'Creator' and @ascend = 'asc') THEN CB.email
WHEN (@SearchType = 'Category' and @ascend = 'asc') THEN bt.bundle_type_name
ELSE CB.Bundle_Name
END
does not.

An easy way to get round it (maybe) is
select i from
(select i from #a
union
select i from #a) a
order by case when 1=1 then i else i end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

drolfson
Starting Member

3 Posts

Posted - 2004-11-09 : 12:32:36

THANKS nr!! I had to make two temp tables (one for each half of the IF, but that worked. Now, why oh why didn't I think of a temp table. . . next time I spose.

*****************
Thanx,

Don

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources