Solved

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

Posted on 2011-02-17
3
325 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
3 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 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 40

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

828 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