Informix多列多行下获取最新日期的查询问题

问题根源 #

Informix对CASE表达式的返回类型要求严格，不仅要同为datetime类型，精度也必须完全一致。你之前用TO_DATE生成默认日期时，返回的datetime精度（比如YEAR TO MINUTE）大概率和date_entered/date_updated的实际精度（比如YEAR TO SECOND）不匹配，这才触发了IX000 -800错误。

解决方案 #

直接使用带明确精度的datetime字面量替代TO_DATE生成默认日期，同时结合COALESCE和GREATEST处理null值，完美实现需求：

1. 单条记录的最新日期计算 #

先将date_entered和date_updated的null值替换为默认日期，再取两者的最大值：

GREATEST(
    COALESCE(date_entered, DATETIME '1950-01-01 00:00:00' YEAR TO SECOND),
    COALESCE(date_updated, DATETIME '1950-01-01 00:00:00' YEAR TO SECOND)
) AS row_latest_date

• 注意：YEAR TO SECOND需替换成你表中date_entered/date_updated实际的datetime精度（比如YEAR TO MINUTE），确保和原列完全一致。

2. 同一group_number的全局最新日期 #

在上述基础上用MAX()聚合即可：

SELECT
    group_number,
    MAX(
        GREATEST(
            COALESCE(date_entered, DATETIME '1950-01-01 00:00:00' YEAR TO SECOND),
            COALESCE(date_updated, DATETIME '1950-01-01 00:00:00' YEAR TO SECOND)
        )
    ) AS group_latest_date
FROM your_table
GROUP BY group_number;

替代方案（保留CASE写法） #

如果一定要用CASE，需将TO_DATE的结果强制转换为和原列相同精度的datetime：

CASE
    WHEN date_entered IS NOT NULL OR date_updated IS NOT NULL THEN
        GREATEST(COALESCE(date_entered, DATETIME '1950-01-01 00:00:00' YEAR TO SECOND),
                 COALESCE(date_updated, DATETIME '1950-01-01 00:00:00' YEAR TO SECOND))
    ELSE
        CAST(TO_DATE('1950-01-01 00:00', '%Y-%m-%d %H:%M') AS DATETIME YEAR TO SECOND)
END AS row_latest_date

关键注意点 #

• Informix的CASE表达式要求所有分支返回的datetime精度完全一致，这是硬性规则。
• 使用datetime字面量比TO_DATE更高效，且能直接控制精度，从根源避免类型匹配问题。

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