Link to home
Start Free TrialLog in
Avatar of barnesco
barnesco

asked on

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
Avatar of Dave Baldwin
Dave Baldwin
Flag of United States of America image

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
Avatar of barnesco
barnesco

ASKER

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.
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
>>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
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
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.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok, not what I was expecting.  I'm still wondering where 'Remaining' exists.
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)
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.