FHIR Chat · JPA SearchBuilder Issue · implementers

Stream: implementers

Topic: JPA SearchBuilder Issue

Joe Atterberry (Feb 03 2021 at 22:27):

HAPI Version 5.2 - Oracle 18xe - ojdbc 19.3.0.0

I'm using the hapi-fhir-jpaserver-starter with the new JPA SearchBuilder enabled (the default).
On startup, one of the initial SQL statements that is issued returns: 'ORA-00907: missing right parenthesis' multiple times and the app never starts.
I enabled showing SQL and found this SQL statement generated:

SELECT

    * 
from
    ( SELECT
        RES_ID 
    FROM
        ( SELECT
            t0.RES_ID 
        FROM
            HFJ_RESOURCE t0 
        WHERE
            (
                (
                    t0.RES_TYPE = ?
                ) 
                AND (
                    t0.RES_DELETED_AT IS NULL
                )
            )
         )
         AS RES_ID 
     )
    WHERE
        rownum <= ?

The issue is the 'AS RES_ID'. As far as Oracle is concerned, aliases on sub queries generally are not allowed. If that line is removed, the query works fine. I set the SQL Builder back to legacy mode and don't get this error. I did nothing to the configuration except set it up to use Oracle.

Let me know if more information is needed.

Thanks.

Lloyd McKenzie (Feb 04 2021 at 01:17):

It's probably best to raise this on the #hapi stream

Joe Atterberry (Feb 04 2021 at 18:40):

I raised it on the #hapi stream. Thanks.

Last updated: Apr 12 2022 at 19:14 UTC