升级Pervasive SQL 15.10.031后ORDER BY中IF语句报错求助

问题原因 #

升级到Pervasive SQL 15.10.031后，对UNION查询的ORDER BY子句规则更严格，不允许直接在ORDER BY中引用列名进行计算（比如原写法里的IF(confirmed_date = '', required_date, confirmed_date)），必须将计算逻辑提前到子查询的结果集中。

解决方案 #

将排序用的计算字段提前定义在子查询里，然后在ORDER BY中直接引用该字段即可。

方案1：返回排序字段（简单直接）

SELECT 
    part_number,
    required_date,
    confirmed_date,
    IF(confirmed_date = '', required_date, confirmed_date) AS sort_date
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, sort_date

方案2：不返回排序字段（保持原结果集结构）

如果不需要将sort_date作为查询结果返回，可以嵌套一层子查询：

SELECT part_number, required_date, confirmed_date
FROM (
    SELECT 
        part_number,
        required_date,
        confirmed_date,
        IF(confirmed_date = '', required_date, confirmed_date) AS sort_date
    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
) AS t2
ORDER BY part_number, sort_date

说明 #

两种方案都通过将IF逻辑提前到子查询中生成一个专门用于排序的字段，避开了新版本Pervasive SQL对UNION查询ORDER BY子句的限制，同时满足先按part_number排序，再根据confirmed_date是否为空选择排序依据的需求。

内容的提问来源于stack exchange，提问作者Conny Olsson