H2数据库执行SQL报语法错误，同语句在Teradata运行正常

问题根因 #

H2数据库中ROWNUM是系统内置的保留关键字，你直接将窗口函数的别名设置为rownum属于关键字冲突，因此会抛出语法错误提示期望标识符。Teradata未将ROWNUM列为保留关键字，所以原SQL可以正常运行。

修复方案 #

方案1（推荐：更换别名避免关键字冲突） #

直接将窗口函数别名替换为非保留字即可，兼容性最好，适配所有数据库场景：

SELECT
    cin,
    reviewDate,
    kycStatus
FROM
    (
        SELECT
            ppc.prty_idnt_num  AS cin,
            prty_sts_strt_dttm AS reviewDate,
            prty_sts_cd        AS kycStatus,
            ROW_NUMBER()
            OVER(PARTITION BY ps.prty_id
                 ORDER BY
                     prty_sts_strt_dttm DESC
            ) AS rn
        FROM
            public.party_status ps
            LEFT JOIN public.party_position_current ppc ON ppc.prty_id = ps.prty_id
        WHERE
                UPPER(prty_sts_clsn_cd) = 'KYC'
            AND ppc.prty_idnt_num = :cin
    ) kyc
 WHERE
    rn = 1 OR rn IS NULL;

方案2（不推荐：强制使用保留字作为别名） #

如果业务要求必须保留rownum作为别名，可以用双引号包裹别名强制H2将其识别为自定义标识符，注意查询条件里的别名也需要加双引号，否则大小写敏感的H2会识别失败：

SELECT
    cin,
    reviewDate,
    kycStatus
FROM
    (
        SELECT
            ppc.prty_idnt_num  AS cin,
            prty_sts_strt_dttm AS reviewDate,
            prty_sts_cd        AS kycStatus,
            ROW_NUMBER()
            OVER(PARTITION BY ps.prty_id
                 ORDER BY
                     prty_sts_strt_dttm DESC
            ) AS "rownum"
        FROM
            public.party_status ps
            LEFT JOIN public.party_position_current ppc ON ppc.prty_id = ps.prty_id
        WHERE
                UPPER(prty_sts_clsn_cd) = 'KYC'
            AND ppc.prty_idnt_num = :cin
    ) kyc
 WHERE
    "rownum" = 1 OR "rownum" IS NULL;

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