"Invalid column name", Order By on Alias of subquery

alias, sql, sql-order-by, stored-procedures

Solution

You can't order by the alias in that way.

The first option is to repeat the code. Note: Just because you repeat the code, the SQL Engine isn't so naive as to execute it again, it re-uses 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 using a sub query...

  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

Problem

I've created a stored procedure where i want to add an alternative order by clause. The problen is that the query failed on a "Invalid column name 'aantal regels'" Here is the query 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 with it!

Original source