Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Posted on 2011-02-17
3
Medium Priority
?
339 Views
Last Modified: 2012-05-11
@OrderBy passed into proc as either 'ZIP' or 'NAME'

RATS! Can't figure out what's wrong! Error Msg says
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Error points at line #1 below ...

        Select distinct wrap.L0, wrap.L1, wrap.L2, wrap.L3, wrap.L4, wrap.ZIPCODE 
        from
        (
            SELECT 
                dbo.PeopleMailingNameCalc(LTC.People2ID) as L0,
                dbo.AddressCollapseReturnL1L2L3L4(1, null, LTC.PeopleAddress2ID) as L1,
                dbo.AddressCollapseReturnL1L2L3L4(2, null, LTC.PeopleAddress2ID) as L2,
                dbo.AddressCollapseReturnL1L2L3L4(3, null, LTC.PeopleAddress2ID) as L3,
                dbo.AddressCollapseReturnL1L2L3L4(4, null, LTC.PeopleAddress2ID) as L4,
                (Select (isnull(Zip,'') + ' ' + isnull(Zip4,'')) 
                    FROM dbo.PeopleAddresses2 where PeopleAddress2ID=LTC.PeopleAddress2ID)
                    as ZIPCODE
            FROM Membership2 
                INNER JOIN Body2 
                ON Membership2.BodyId = Body2.BodyId 
                INNER JOIN MembershipStatus2 
                ON Membership2.MembershipStatusId = MembershipStatus2.MembershipStatusId 
                INNER JOIN People2 
                ON Membership2.People2Id = People2.People2ID
                INNER JOIN @LocTableC LTC
                ON LTC.People2ID = People2.People2ID
            WHERE     
                (People2.DateOfDeath IS NULL)
            AND 
                (Membership2.People2ID in (select People2ID from @LocTableC)) 
        ) as wrap
        ORDER BY CASE 
            WHEN @OrderBy = 'ZIP' THEN wrap.ZIPCODE
            ELSE wrap.L0
            END

Open in new window

0
Comment
Question by:volking
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 34921326
use this
Select wrap.L0, wrap.L1, wrap.L2, wrap.L3, wrap.L4, wrap.ZIPCODE 
        from
        (
            SELECT 
                dbo.PeopleMailingNameCalc(LTC.People2ID) as L0,
                dbo.AddressCollapseReturnL1L2L3L4(1, null, LTC.PeopleAddress2ID) as L1,
                dbo.AddressCollapseReturnL1L2L3L4(2, null, LTC.PeopleAddress2ID) as L2,
                dbo.AddressCollapseReturnL1L2L3L4(3, null, LTC.PeopleAddress2ID) as L3,
                dbo.AddressCollapseReturnL1L2L3L4(4, null, LTC.PeopleAddress2ID) as L4,
                (Select (isnull(Zip,'') + ' ' + isnull(Zip4,'')) 
                    FROM dbo.PeopleAddresses2 where PeopleAddress2ID=LTC.PeopleAddress2ID)
                    as ZIPCODE
            FROM Membership2 
                INNER JOIN Body2 
                ON Membership2.BodyId = Body2.BodyId 
                INNER JOIN MembershipStatus2 
                ON Membership2.MembershipStatusId = MembershipStatus2.MembershipStatusId 
                INNER JOIN People2 
                ON Membership2.People2Id = People2.People2ID
                INNER JOIN @LocTableC LTC
                ON LTC.People2ID = People2.People2ID
            WHERE     
                (People2.DateOfDeath IS NULL)
            AND 
                (Membership2.People2ID in (select People2ID from @LocTableC)) 
        ) as wrap
        GROUP BY wrap.L0, wrap.L1, wrap.L2, wrap.L3, wrap.L4, wrap.ZIPCODE
        ORDER BY CASE 
            WHEN @OrderBy = 'ZIP' THEN wrap.ZIPCODE
            ELSE wrap.L0
            END

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34922445
Move DISTINCT into sub query.
Select  wrap.L0, wrap.L1, wrap.L2, wrap.L3, wrap.L4, wrap.ZIPCODE 
        from
        (
            SELECT distinct
                dbo.PeopleMailingNameCalc(LTC.People2ID) as L0,
                dbo.AddressCollapseReturnL1L2L3L4(1, null, LTC.PeopleAddress2ID) as L1,
                dbo.AddressCollapseReturnL1L2L3L4(2, null, LTC.PeopleAddress2ID) as L2,
                dbo.AddressCollapseReturnL1L2L3L4(3, null, LTC.PeopleAddress2ID) as L3,
                dbo.AddressCollapseReturnL1L2L3L4(4, null, LTC.PeopleAddress2ID) as L4,
                (Select (isnull(Zip,'') + ' ' + isnull(Zip4,'')) 
                    FROM dbo.PeopleAddresses2 where PeopleAddress2ID=LTC.PeopleAddress2ID)
                    as ZIPCODE
            FROM Membership2 
                INNER JOIN Body2 
                ON Membership2.BodyId = Body2.BodyId 
                INNER JOIN MembershipStatus2 
                ON Membership2.MembershipStatusId = MembershipStatus2.MembershipStatusId 
                INNER JOIN People2 
                ON Membership2.People2Id = People2.People2ID
                INNER JOIN @LocTableC LTC
                ON LTC.People2ID = People2.People2ID
            WHERE     
                (People2.DateOfDeath IS NULL)
            AND 
                (Membership2.People2ID in (select People2ID from @LocTableC)) 
        ) as wrap
        ORDER BY CASE 
            WHEN @OrderBy = 'ZIP' THEN wrap.ZIPCODE
            ELSE wrap.L0
            END

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34924977
are both the columns wrap.ZIPCODE & wrap.L0 of the same datatype?

if not you need to cast/convert them to a common type...

e.g.

ORDER BY case WHEN @OrderBy = 'ZIP' THEN right('0000000000'+convert(varchar(10),wrap.ZIPCODE),10)
            ELSE wrap.L0
            END

to make them both character ...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question