MSSQL CASE after ORDER BY not working with alias

Question

Hello Stack Overflow Community!

I'm sitting here with my colleague and we're trying to solve a problem we currently have with MSSQL 2014.

We have a table with a number of columns, where two of them are containing a Date. Just for comprehension; one date ('Liefertermin') shows when/if it actually was delivered and the other one ('FreiesDatum1') shows when it is planned to be delivered.

If 'Liefertermin' is not empty then it should show up and otherwise 'FreiesDatum1' should appear. We solved this with the following query which works as it should:

SELECT
    CASE
       WHEN Liefertermin is null THEN cast(FreiesDatum1 as date)
       ELSE Liefertermin
    END as SortDate
FROM Beleg

There are of course some other columns in the SELECT-query, but those aren't necessary for the problem. We wanted to have the sorting dynamic, so we could choose between different types. For that we declared a variable (@Sort) at the beginning and set up a CASE-Statement after the 'order by'.

And now there's the problem we're facing:

We are unable to order by the 'SortDate', that is selected above, if it stands in the CASE-statement. If we just do order by 'SortDate' it works without any problem.

This is our attempt, that does not work:

order by CASE
            when @Sort=1 then 'SortDate'
            when @Sort=2 then 'Liefertermin'
            when @Sort=3 then 'Name'
          END

@Sort=1 doesn't, but @Sort=2 and @Sort=3 do work so I guess there's no Problem with the CASE itself. When removing the marks I get an error, that the column 'SortDate' does not exist.

We already tried changing the alias to [SortDate] or 'SortDate' without any success and also tried using a derived table but we got the same result.

We searched through these forums here and tried every solution that worked for other problems with CASE-statements, but none of these worked.

Really hope for help here! Greetings 3m7ecc

EDIT:

Here is the full SQL-Query

Declare @Sort integer;
Set @Sort = 1

select
    Beleg.Belegnummer,
    Beleg.Belegtyp,
    Beleg.Datum,
    wp.Projekt,
    wp.Bezeichnung as 'Projektbezeichnung',
    BELEG.Adressnummer,
    BELEG.Firma,
    BELEG.Ort,
    convert(varchar(10),BELEG.Liefertermin,104) as Liefertermin,
    convert(varchar(10),BELEG.FreiesDatum1,104) as iLiefertermin,
    Beleg.Netto,
    BELEG.Status,
    cast(BELEG.Datum as date) as Erfassungsdatum,
    CASE
        when BELEG.Liefertermin is null then Beleg.FreiesDatum1
        else BELEG.Liefertermin
    END as SortDate
from BELEG
left join WPROJEKT as WP on (wp.Id = BELEG.Projekt)
    where 
    (('01.09.2017' is null or convert(varchar(10),BELEG.Liefertermin,104) >= '01.09.2017')
        and ('31.10.2017' is null or convert(varchar(10),BELEG.Liefertermin,104) <= '31.10.2017')) 
    and Beleg.belegtyp = 'B'
    and ((BELEG.Liefertermin is null
    and BELEG.FreiesDatum1 is null)
    or (BELEG.Liefertermin <= GETDATE() or BELEG.FreiesDatum1 <= GETDATE()))

ORDER BY
CASE WHEN @Sort = 1 then SortDate END DESC,
CASE WHEN @Sort = 2 then Liefertermin END DESC,
CASE WHEN @Sort = 3 then BELEG.Belegnummer END DESC

Answer 1

You cannot use aliased columns in the ORDER BY clause because sorting logically it happens before the SELECT clause. You could use column numbers, e.g. ORDER BY 9 would sort the output by your 'Liefertermin' column, but that is considered a bad practice. Best way around it would be to put everything inside a subquery, like:

SELECT * FROM
(SELECT... -- your whole select here without the ORDER BY clause
 ) as t1
ORDER BY
    CASE WHEN @Sort = 1 then SortDate END DESC
    CASE WHEN @Sort = 2 then Liefertermin END DESC
    CASE WHEN @Sort = 3 then Name END DESC