Unable to identify syntax issue with H2 database

Question

Query:

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;

Error:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "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 = ?    ) KYC WHERE    ROWNUM = 1 OR ROWNUM IS NULL"; expected "identifier"; SQL statement:
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 = ?    ) kyc WHERE    rownum = 1 OR rownum IS NULL [42001-200]

The query works fine in DB (Teradata) but fails in H2 (used as a test database). I cannot figure what's wrong with the syntax. I suspect row_number() to be the culprit but not sure. Can anyone help fix the issue?

Answer 1

ROWNUM is a function name in H2. So you can not use ROWNUM directly. You can either change ROWNUM to any other variables or change your query like this

SELECT
    cin,
    reviewDate,
    kycStatus,
    `ROWNUM`
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 = 1
                ) kyc
WHERE
    `ROWNUM` = 1
    OR `ROWNUM` IS NULL;