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

Question

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!

Answer 1

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

Answer 2

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