Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-02-17
3
Medium Priority
?
348 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 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline

579 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