Solved

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

Posted on 2011-02-17
3
333 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 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 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

626 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