Missing Keyword WITHIN Hibernate

user_5259325 0
Viewed 97

I'm adding a @Formula in my model/entity class in order to make a request for each element.

@Formula("(select (LISTAGG(ver.ACC_DESC_ES, ', ') WITHIN GROUP (ORDER BY ACC_ID)) from TSEN_ACCIONES_BIB acc, VSEN_ELC_REVISION ver where ELC_ID = acc.ACC_ELC_ID and acc.ACC_ACTIVA='S')")

The problem is that Hibernate takes WITHIN as a table column and it generates a query without it.

select
        count(*)
    from
        TSEN_ACCIONES_BIB acc
    where
        revisionit0_.ELC_ID = acc.ACC_ELC_ID
        and acc.ACC_ACTIVA='N'
        and (
            acc.ACC_RESALTAR='S'
            or acc.ACC_RESALTAR='R'
        )) as formula3_0_,
    (select
        LISTAGG(revisionit0_.ACC_DESC,
        ', ') revisionit0_.WITHIN
    GROUP (ORDER BY
        revisionit0_.ACC_ID)
    from
        TSEN_ACCIONES_BIB acc
    where
        revisionit0_.ELC_ID = acc.ACC_ELC_ID
        and acc.ACC_ACTIVA='S') as formula4_0_,

As you can see, WITHIN GROUP is changed to revisionit0_.WITHIN GROUP.

How can I deal with it? Thanks

oracle hibernate sql java
2 Answers

This is not hibernate issue but a problem with your query. Inner query should be named or aliased and you are missing a name so WITHIN is construed as name.

Correct line will be

@Formula("(select (LISTAGG(ver.ACC_DESC_ES, ', ') TEMPCOLNAME WITHIN GROUP (ORDER BY ACC_ID)) from TSEN_ACCIONES_BIB acc, VSEN_ELC_REVISION ver where ELC_ID = acc.ACC_ELC_ID and acc.ACC_ACTIVA='S')")
user_11232260

I finally solved by myself changing the WITHIN with \00WITHIN.

Maybe @DhruvJoshi answer will work too.

 @Formula("(select (LISTAGG(ver.ACC_DESC_ES, ', ') \00WITHIN GROUP (ORDER BY ACC_ID)) from TSEN_ACCIONES_BIB acc, VSEN_ELC_REVISION ver where ELC_ID = acc.ACC_ELC_ID and acc.ACC_ACTIVA='S')")
user_52593250
Related