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.
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