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.

What error are you getting? Are you using v15 SP1 or the original v15? I tried it using v15 SP1, and didn't get any errors. — mirtheil, Oct 21 '22 at 14:39
The error was something like "reference to column not allowed in order by with union". But I only have the UNIONs inside a subquery and the ORDER BY in the outer query. The error disappeared when I removed the IF statement. Do you know how to get version information via ODBC? — Conny Olsson, Oct 24 '22 at 14:07
The exact error message was "Reference to column name not allowed in ORDER BY with UNION". — Conny Olsson, Oct 24 '22 at 14:16
What's the rest of your SQL? What shows in the Help | About, specifically the "Zen Install Version", in the Zen Control Center? — mirtheil, Oct 27 '22 at 19:21
@mirtheil I have updated the question with a complete SQL and version (15.10.031). — Conny Olsson, Oct 28 '22 at 09:08
With your full SQL, I can confirm the behavior you are seeing. I would suggest contacting Actian and opening a support ticket. — mirtheil, Oct 28 '22 at 13:49

score 1 · Accepted Answer · answered Oct 21 '22 at 09:49

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

Pervasive SQL order by with if

1 Answers1