How to use Calculated Column in SQL ORDER BY ASC, while filtering NULL values to the bottom

Question

I am trying to use ORDER BY ASC to sort my query based on a calculated column. I also need NULL values to be sorted to the bottom of the query. I am getting the following error on the last line of my query:

Invalid column name 'PLC ADDRESS'.

How do I resolve this error to fix my sorted query?

SELECT 
    CASE WHEN IO_type.IO_acronym = 'PWR' THEN
        NULL
    ELSE
        ISNULL(CAST(FORMAT(source.rack_number, '00')  AS VARCHAR(MAX)),'XX') + ':' + 
        ISNULL(CAST(FORMAT(slot.index_in_PLC,  '00')  AS VARCHAR(MAX)),'XX') + ':' + 
        ISNULL(CAST(FORMAT(IOs.channel,        '00')  AS VARCHAR(MAX)),'XX') 
    END AS [PLC ADDRESS]

    -- More unrelated query columns here.

    FROM controls.IO_table                  AS IOs
    LEFT JOIN controls.slot_table           AS slot     ON slot.ID_auto     = IOs.from_PLC_slot_ID
    LEFT JOIN controls.source_standard      AS source   ON source.ID_auto   = slot.control_source_ID
    LEFT JOIN enum.IO_type                  AS IO_type  ON IO_type.ID_auto  = IOs.IO_type_ID

    -- ORDER BY [PLC ADDRESS] ASC -- This works, but leaves NULL values at the top
    ORDER BY CASE WHEN [PLC ADDRESS] IS NULL THEN 1 ELSE 0 END, [PLC ADDRESS]
    --                 ^^^^^^^^^^^^^ Error on this column. "Invalid column name 'PLC ADDRESS'"

Answer 1

Of course there is a way. You can use a CROSS APPLY to calculate intermediate (or final) values that can then be referenced elsewhere without the need to repeat the calculations.

SELECT 
    PA.[PLC ADDRESS]

    -- More unrelated query columns here.

FROM controls.IO_table                  AS IOs
LEFT JOIN controls.slot_table           AS slot     ON slot.ID_auto     = IOs.from_PLC_slot_ID
LEFT JOIN controls.source_standard      AS source   ON source.ID_auto   = slot.control_source_ID
LEFT JOIN enum.IO_type                  AS IO_type  ON IO_type.ID_auto  = IOs.IO_type_ID
CROSS APPLY (
    SELECT
        CASE WHEN IO_type.IO_acronym = 'PWR' THEN
            NULL
        ELSE
            ISNULL(CAST(FORMAT(source.rack_number, '00')  AS VARCHAR(MAX)),'XX') + ':' + 
            ISNULL(CAST(FORMAT(slot.index_in_PLC,  '00')  AS VARCHAR(MAX)),'XX') + ':' + 
            ISNULL(CAST(FORMAT(IOs.channel,        '00')  AS VARCHAR(MAX)),'XX') 
        END AS [PLC ADDRESS]
) PA
-- ORDER BY [PLC ADDRESS] ASC -- This works, but leaves NULL values at the top
ORDER BY
    CASE WHEN PA.[PLC ADDRESS] IS NULL THEN 1 ELSE 0 END,
    PA.[PLC ADDRESS]
--                 ^^^^^^^^^^^^^ Error on this column. "Invalid column name 'PLC ADDRESS'"