How to use string_agg?

Question

I have this method:

   public List<Object[]> allIncomeChannels(final List<String> list) {

        return entityManager.createQuery(
                "select  string_agg(a.logicalUnitIdent,',') idents, a.incomeChannel.code, a.logicalUnitIdent, a.keyword from IncomeChannelMapEntity a GROUP BY a.incomeChannel.code, a.logicalUnitCode,a.keyword",
                Object[].class).getResultList();

    }

But im getting error :

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: idents near line 1, column 44

Any suggestion how can i fix this? Im using postgres

Please edit your question to include the database system you are using. — Progman
– Progman, Commented Aug 29, 2019 at 18:39

Mushif Ali Nawaz · Accepted Answer · 2019-08-29 19:36:05Z

1

Try using createNativeQuery() method with a native query. Here is your updated code:

public List<Object[]> allIncomeChannels(final List<String> list) {
    return entityManager.createNativeQuery(
            "select  string_agg(a.logicalUnitIdent,',') idents, a.incomeChannel.code, a.logicalUnitIdent, a.keyword from IncomeChannelMapEntity a GROUP BY a.incomeChannel.code, a.logicalUnitCode,a.keyword")
            .getResultList();
}

Replace the query with a native query here.

edited Aug 29, 2019 at 19:36

answered Aug 29, 2019 at 18:54

Mushif Ali Nawaz

3,9163 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

None Over a year ago

when im using this query im getting error : No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode \r\n +-[METHOD_CALL] MethodNode........

None Over a year ago

i dont know why but now im getting this error : ERROR: relation "incomechannelmapentity" does not exist

Mushif Ali Nawaz Over a year ago

What is your table name?

None Over a year ago

table is call r_income_channel_map , but enttiy in java is IncomeChannelMapEntity

None Over a year ago

Let us continue this discussion in chat.

|

Sounak Saha · Accepted Answer · 2019-08-29 18:39:46Z

0

In your query 'as' is missing after string_agg(a.logicalUnitIdent,',') function. It would be like this

"select  string_agg(a.logicalUnitIdent,',') as idents, a.incomeChannel.code,......"

answered Aug 29, 2019 at 18:39

Sounak Saha

9531 gold badge10 silver badges25 bronze badges

3 Comments

None Over a year ago

if i use as im getting another error : No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode

Sounak Saha Over a year ago

I believe you are using HQL and IncomeChannelMapEntity is your Java object. Is 'idents' field available in your java object?

None Over a year ago

i dont have that filed, its alias

Collectives™ on Stack Overflow

How to use string_agg?

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related