Invalid column name, Order by subquery alias

Question

I created a stored procedure where I want to add an alternative order by order. The problem is that the request ended with the error "Invalid column name" aantal regels "

Here is the request that I have now.

SELECT l.lead_id, l.afdeling_id, l.advertentie_id, l.naam, l.type, l.status, l.herkomst, l.aanmaakdatum, l.klant_id, l.overigegegevens, af.afdelingsnaam, (SELECT COUNT(lead_regel_id) FROM Lead_regel As lr Where Lr.lead_id = l.lead_id And lr.status <> 100 ) AS aantal_regels, (SELECT COUNT(lead_id) FROM Lead_unread As lu Where lu.lead_id = l.lead_id And lu.user_id = @uid ) As lead_ongelezen, (SELECT COUNT(lru.lead_regel_id) FROM Lead_regel As lr2 INNER JOIN Lead_regel_unread As lru ON lr2.lead_regel_id = lru.lead_regel_id Where lr2.lead_id = l.lead_id And lru.user_id = @uid And lr2.status <> 100 ) As lead_regel_ongelezen FROM Lead AS l INNER JOIN Afdeling AS af ON l.afdeling_id = af.afdeling_id WHERE l.afdeling_id = @aid AND l.status <> 100 ORDER BY CASE WHEN @orderby = 'default' THEN l.aanmaakdatum END DESC, CASE WHEN @orderby = 'type' THEN l.type END ASC, CASE WHEN @orderby = 'naam' THEN l.naam END ASC, CASE WHEN @orderby = 'reacties' THEN aantal_regels END DESC 

Hope someone can help me!

Answer 1

You cannot order an alias in this way.

The first option is to repeat the code. Note. Just because you repeat the code, SQL Engine is not so naive as to run it again, it reuses the results.

 ORDER BY CASE WHEN @orderby = 'default' THEN l.aanmaakdatum END DESC, CASE WHEN @orderby = 'type' THEN l.type END ASC, CASE WHEN @orderby = 'naam' THEN l.naam END ASC, CASE WHEN @orderby = 'reacties' THEN (SELECT COUNT(lead_regel_id) FROM Lead_regel As lr WHERE Lr.lead_id = l.lead_id And Lr.status <> 100 ) END DESC 

Or so it all with a subquery ...

  SELECT * FROM ( yourQuery ) AS sub_query ORDER BY CASE WHEN @orderby = 'default' THEN aanmaakdatum END DESC, CASE WHEN @orderby = 'type' THEN type END ASC, CASE WHEN @orderby = 'naam' THEN naam END ASC, CASE WHEN @orderby = 'reacties' THEN aantal_regels END DESC 

Answer 2

The short version is that although ORDER BY itself can use aliases quite happily, the CASE statement in ORDER BY cannot. CASE statements will be evaluated as part of the SELECT, and thus, before any aliases are taken into account.