Pervasive SQL order by with if

Question

In Pervasive SQL 11 I could use a IF statement in the ORDER BY:

SELECT * 
FROM ( 
    SELECT 
    D1001 as 'part_number', 
    '' as 'required_date', 
    '' as 'confirmed_date'
    FROM PULAGER 
    WHERE 
    D1001 LIKE '1121%' 
    
    UNION 
    
    SELECT 
    D5410 as 'part_number', 
    D5511 as 'required_date', 
    D5513 as 'confirmed_date'
    FROM PUIKOKRO 
    WHERE 
    D5410 LIKE '1121%' 
) as t1
ORDER BY part_number, IF (confirmed_date = '', required_date, confirmed_date)

But after an upgrade version 15.10.031, I get the error "Reference to column name not allowed in ORDER BY with UNION". No error if I remove the IF statement. Any suggestions?

First order by part_number and then order by required_date or confirmed_date depending on the state of confirmed_date.

Answer 1

I solved it by moving the IF statement to the SELECT to create a new column 'sort_date' and wrapping it all with another SELECT. Doesn't feel like the most beautiful solution, but it works.

SELECT * FROM (
    SELECT t1.*, IF (confirmed_date = '', required_date, confirmed_date) as 'sort_date' FROM t1
) ORDER BY part_number, sort_date