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

@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

LVL 5
volkingAsked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:
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
 
SharathData EngineerCommented:
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
 
LowfatspreadCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.