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
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
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
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
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)
I have chosen date style 121 see these tables for other options
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
nb: when casting/converting dates to varchar (and will sort on this) then you want EXPLICITLY force the YYYY MM DD ... sequenceI 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:
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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.
http://msdn.microsoft.com/en-us/library/e9zc0283%28v=vs.80%29.aspx