CODEWITHSUNDEEP
javasqloracle-databasehibernate
Harshit Chawla
Harshit Chawla

Reputation: 113

ORA-00979: not a GROUP BY expression

I made function in my DAO implementation for running query

SQL QUERY : 

  SELECT pct.IA_FILING_METHOD_CODE_ID, COUNT(*) 
            FROM Ia_Dossier pct 
            WHERE 1=1 and pct.INTERNATIONAL_FILING_DATE IS NOT NULL 
            AND pct.INTERNATIONAL_FILING_DATE>='11-SEP-08' 
            AND pct.INTERNATIONAL_FILING_DATE<='11-SEP-16' 
            GROUP BY pct.IA_FILING_METHOD_CODE_ID 
            HAVING pct.IA_FILING_METHOD_CODE_ID IS NOT NULL

This is running fine in db but giving error when run from hibernate code.

FUNCTION:

    @Override
    public List<Object[]> findStatsByROGroupedByFileFormat(Date startDate, Date endDate,String RO, String groupBy) {

        StringBuilder jpqlQuery = new StringBuilder();
        jpqlQuery.append("Select pct.").append(groupBy).append(",count(*) from IaDossier as pct where 1=1 and pct.internationalFilingDate is not null");
        if(startDate!=null)
        {
            jpqlQuery.append(" and pct.internationalFilingDate >=:startDate");
        }
        if(endDate!=null)
        {
            jpqlQuery.append(" and pct.internationalFilingDate <=:endDate");
        }

        if(groupBy!=null && RO!=""){
            jpqlQuery.append(" group by pct.").append(groupBy);
            jpqlQuery.append(" having pct.").append(groupBy).append(" is not null");
        }
System.out.println(jpqlQuery+"hc");
        Query q = entityManager.createQuery(jpqlQuery.toString());
        if(startDate!=null)
        {
            q.setParameter("startDate", startDate);
        }
        if(endDate!=null)
        {
            q.setParameter("endDate", endDate);
        }
        return (List<Object[]>)q.getResultList();
    }

CONSOLE: 

12:09:45,572 INFO  [stdout] (http-localhost-127.0.0.1-8082-2) Select pct.iaFilingMethod,count(*) from IaDossier as pct where 1=1 and pct.internationalFilingDate is not null and pct.internationalFilingDate >=:startDate and pct.internationalFilingDate <=:endDate group by pct.iaFilingMethod having pct.iaFilingMethod is not null

12:09:45,730 INFO  [stdout] (http-localhost-127.0.0.1-8082-2) Hibernate: select iadossier0_.IA_FILING_METHOD_CODE_ID as col_0_0_, count(*) as col_1_0_, iafilingme1_.IA_FILING_METHOD_CODE_ID as IA1_52_, iafilingme1_.EN_NAME as EN2_52_, iafilingme1_.END_DATE as END3_52_, iafilingme1_.FR_NAME as FR4_52_, iafilingme1_.IS_EFILING as IS5_52_, iafilingme1_.OFFICE_RESTRICTIONS_APPLY as OFFICE6_52_, iafilingme1_.START_DATE as START7_52_ from IA_DOSSIER iadossier0_ inner join IBPROD.IA_FILING_METHODS iafilingme1_ on iadossier0_.IA_FILING_METHOD_CODE_ID=iafilingme1_.IA_FILING_METHOD_CODE_ID where 1=1 and (iadossier0_.INTERNATIONAL_FILING_DATE is not null) and iadossier0_.INTERNATIONAL_FILING_DATE>=? and iadossier0_.INTERNATIONAL_FILING_DATE<=? group by iadossier0_.IA_FILING_METHOD_CODE_ID having iadossier0_.IA_FILING_METHOD_CODE_ID is not null

12:09:46,076 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-localhost-127.0.0.1-8082-2) SQL Error: 979, SQLState: 42000
12:09:46,078 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-localhost-127.0.0.1-8082-2) ORA-00979: not a GROUP BY expression

I don't know why it's giving this GROUP BY error when the query is running fine in sql developer.

Upvotes: 3

Views: 2114

Answers (3)

Harshit Chawla
Harshit Chawla

Reputation: 113

i finally got to the reason of this error, in my jpa entity IA_FILING_METHOD_CODE_ID was set to fetch.EAGER that's why it was making automatic joins to other table, thanks a lot for the help it moved me to the right direction. :)

Upvotes: 0

sagi
sagi

Reputation: 40481

What are you trying to do??

When you use the HAVING clause you have to use an aggregation functions on the columns specified there, since you are grouping this query. I just changed it to a simple WHERE clause, no need to be specified in the HAVING.

SELECT pct.IA_FILING_METHOD_CODE_ID, COUNT(*) 
FROM Ia_Dossier pct 
WHERE pct.INTERNATIONAL_FILING_DATE IS NOT NULL 
    AND pct.INTERNATIONAL_FILING_DATE>='11-SEP-08' 
    AND pct.INTERNATIONAL_FILING_DATE<='11-SEP-16'
    AND pct.IA_FILING_METHOD_CODE_ID IS NOT NULL
 GROUP BY pct.IA_FILING_METHOD_CODE_ID

The Oracle HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.

Upvotes: 2

user6460963
user6460963

Reputation:

What is the data displayed from the database if you run your sql code? It could be that your pct.IA_FILING_METHOD_CODE_ID is NULL?

You must put all columns of the SELECT in the GROUP BY or use functions on them which compress the results to a single value (like MIN, MAX or SUM).

Try printing the entire jpqlQuery and compare it to the SQL you use in your DB. (Also post both SQL Queries here)

More info about a GROUP BY

Upvotes: 1

Related Questions