Error returning any values on order by statement

The dynamic order by and sort order is not working for "Remaining" which has an ordinal value of 8 in the select statement.

So this works:

ORDER BY 8 ASC

But the below doesn't do anything. Note that if I try to use 'Remaining' as the value in the case statement, I receive the error message: Invalid column name 'Remaining', which why it's ordinal.

@OrderBy varchar(20) = 'Remaining',
@Direction int = 1

      CASE
            WHEN @Direction = 0 THEN NULL
            WHEN @OrderBy = 'StartDate' THEN CAST(StartDate AS varchar(10))      
            WHEN @OrderBy = 'EndDate' THEN CAST(EndDate AS varchar(10))
            WHEN @OrderBy = 'Sessions' THEN CAST(Sessions AS varchar(10))
            WHEN @OrderBy = 'Attended' THEN 7 --Attended
            WHEN @OrderBy = 'Remaining' THEN 8 --Remaining    
      END ASC,
      
      CASE
            WHEN @Direction = 1THEN NULL
            WHEN @OrderBy = 'StartDate' THEN CAST(StartDate AS varchar(10))        
                WHEN @OrderBy = 'EndDate' THEN CAST(EndDate AS varchar(10))
            WHEN @OrderBy = 'Sessions' THEN CAST(Sessions AS varchar(10))
            WHEN @OrderBy = 'Attended' THEN 7 --Attended
            WHEN @OrderBy = 'Remaining' THEN 8 --Remaining  
      END DESC
barnescoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
I believe that if you are not using that 'ordinal', the ORDER BY must refer to a column name in the result set.

http://msdn.microsoft.com/en-us/library/e9zc0283%28v=vs.80%29.aspx
0
barnescoAuthor Commented:
Ok, I figured out how to get the string value to work and not use the ordinal, but the still the 'Remaining' column is not sorting.
0
Dave BaldwinFixer of ProblemsCommented:
Ok, do something very simple like this and see what you get.
SELECT TOP 20 * FROM YourTable ORDER BY Remaining

Open in new window

http://msdn.microsoft.com/en-us/library/h09t6a82%28v=vs.80%29.aspx
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PortletPaulfreelancerCommented:
>>the ORDER BY must refer to a column name in the result set.
that isn't correct, all the following work (here)

-- order by field NOT IN select list
select [Acct_No], [ID] from AnyTable order by [CAT];

-- order by column alias AND field NOT IN select list
select [Acct_No] as blah, [ID] from AnyTable order by [CAT], blah;

-- order by ordinal position AND field NOT IN select list
select [Acct_No] as blah, [ID] from AnyTable order by [CAT],1;


The ORDER BY clause may refer to any field available through the FROM clause
except  in a UNION |ALL| ::  here the order by is restricted of the select list

http://msdn.microsoft.com/en-us/library/e9zc0283%28v=vs.80%29.aspx
Parameters
         ORDER BY Order_Item
                  Specifies the item used to sort the final query result set.
                                    A field in a FROM table or a subquery
0
PortletPaulfreelancerCommented:
back to the question, I believe the issue is that you are expecting the case expression to return varchars for some conditions and integers for other conditions, and this isn't working.

Please try this (I assume Attended & Remaining are numbers, but don't know the width needed)
CASE 
    WHEN @Direction = 0 THEN NULL
    WHEN @OrderBy = 'StartDate' THEN CONVERT(varchar,StartDate, 121)       
    WHEN @OrderBy = 'EndDate'   THEN CONVERT(varchar,EndDate, 121) 
    WHEN @OrderBy = 'Sessions'  THEN CONVERT(varchar,Sessions)
    WHEN @OrderBy = 'Attended'  THEN RIGHT('0000000000' + CONVERT(varchar,Attended, 10)
    WHEN @OrderBy = 'Remaining' THEN RIGHT('0000000000' + CONVERT(varchar,Remaining, 10)
END ASC,

CASE 
    WHEN @Direction = 1 THEN NULL
    WHEN @OrderBy = 'StartDate' THEN CONVERT(varchar,StartDate, 121)        
    WHEN @OrderBy = 'EndDate'   THEN CONVERT(varchar,EndDate, 121) 
    WHEN @OrderBy = 'Sessions'  THEN CONVERT(varchar,Sessions)
    WHEN @OrderBy = 'Attended'  THEN RIGHT('0000000000' + CONVERT(varchar,Attended, 10)
    WHEN @OrderBy = 'Remaining' THEN RIGHT('0000000000' + CONVERT(varchar,Remaining, 10)
END DESC

Open in new window

nb: when casting/converting dates to varchar (and will sort on this) then you want EXPLICITLY force the YYYY MM DD ... sequence
I have chosen date style 121 see these tables for other options
0
PortletPaulfreelancerCommented:
OR
perhaps to avoid excessive type conversions, you could introduce further (simpler) case expressions like this:
CASE 
    WHEN @Direction = 0 THEN NULL
    WHEN @OrderBy = 'StartDate' THEN CONVERT(varchar,StartDate, 121)       
    WHEN @OrderBy = 'EndDate'   THEN CONVERT(varchar,EndDate, 121) 
    WHEN @OrderBy = 'Sessions'  THEN CONVERT(varchar,Sessions)
    ELSE NULL
END ASC,

CASE 
    WHEN @Direction = 1 THEN NULL
    WHEN @OrderBy = 'StartDate' THEN CONVERT(varchar,StartDate, 121)        
    WHEN @OrderBy = 'EndDate'   THEN CONVERT(varchar,EndDate, 121) 
    WHEN @OrderBy = 'Sessions'  THEN CONVERT(varchar,Sessions)
    ELSE NULL
END DESC,

CASE 
    WHEN @Direction = 0 THEN NULL
    WHEN @OrderBy = 'Attended'  THEN Attended
    WHEN @OrderBy = 'Remaining' THEN Remaining  
    ELSE NULL
END ASC,

CASE 
    WHEN @Direction = 1 THEN NULL
    WHEN @OrderBy = 'Attended'  THEN Attended
    WHEN @OrderBy = 'Remaining' THEN Remaining  
    ELSE NULL
END DESC

Open in new window

note I have removed the 'ordinals' in favour of the column names/aliases (I prefer not to use ordinals) but this assumes Attended/Remaining aer the same data types.
0
PortletPaulfreelancerCommented:
OR (probably best), to avoid type conversions:
        CASE
            WHEN @Direction = 0 THEN NULL
            WHEN @OrderBy = 'StartDate' THEN StartDate
            WHEN @OrderBy = 'EndDate'   THEN EndDate
            ELSE NULL
        END ASC,

        CASE
            WHEN @Direction = 1THEN NULL
            WHEN @OrderBy = 'StartDate' THEN StartDate
            WHEN @OrderBy = 'EndDate'   THEN EndDate
            ELSE NULL
        END DESC,

        CASE
            WHEN @Direction = 0 THEN NULL
            WHEN @OrderBy = 'Sessions'  THEN Sessions
            ELSE NULL
        END ASC,

        CASE
            WHEN @Direction = 1THEN NULL
            WHEN @OrderBy = 'Sessions'  THEN Sessions
            ELSE NULL
        END DESC,

        CASE
            WHEN @Direction = 0 THEN NULL
            WHEN @OrderBy = 'Attended'  THEN Attended
            WHEN @OrderBy = 'Remaining' THEN Remaining
            ELSE NULL
        END ASC,

        CASE
            WHEN @Direction = 1THEN NULL
            WHEN @OrderBy = 'Attended'  THEN Attended
            WHEN @OrderBy = 'Remaining' THEN Remaining
            ELSE NULL
        END DESC

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
Ok, not what I was expecting.  I'm still wondering where 'Remaining' exists.
0
PortletPaulfreelancerCommented:
lines 29/30 and 36/37 could use ordinals instead

            WHEN @OrderBy = 'Attended'  THEN 7 --Attended
            WHEN @OrderBy = 'Remaining' THEN 8 --Remaining

I have made the assumption (possibly v.wrong) that there are columns called [Attended] & [Remaining].

I don't like using ordinals for order by (if someone change the select list unexpected results can occur)
0
Scott PletcherSenior DBACommented:
Need to see the SELECT code that generates the "Remaining" column and the other columns.  If the column is explicitly aliased as "Remaining", you should be able to ORDER BY it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.