CODEWITHSUNDEEP
javajpaspring-data-jpa
Anders Metnik
Anders Metnik

Reputation: 6237

"Unknown column" with JPA on AS400 db2

I'm receiving the following error:

could not execute query; SQL [select DFBOGARM, sum(KVANTUM1) as KVANTUM1, (sum(DSTOMK) * 1000 / sum(KVANTUM1)) as DSTOMK, (sum(DSTAKK) * 1000 / sum(KVANTUMAKK)) as DSTAKK from NHODATA.ERHDSTV1 where DFSALVAR in(52, 55) group by DFBOGARM order by DFBOGARM]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query

Caused by: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: An undefined column name was detected.

Query implementation:

public interface DistributionRepository extends JpaRepository<ERHDSTV1, Long> {

@Query(value="select  "
        + "    DFBOGARM, "
        + "    sum(KVANTUM1) as KVANTUM1, "
        + "    (sum(DSTOMK) * 1000 / sum(KVANTUM1)) as DSTOMK, "
        + " (sum(DSTAKK) * 1000 / sum(KVANTUMAKK)) as DSTAKK "
        + "from NHODATA.ERHDSTV1 "
        + "where DFSALVAR in(52, 55) "
        + "group by DFBOGARM "
        + "order by DFBOGARM", nativeQuery = true)
List<ERHDSTV1> findSummedValuesFor52and55();}

Entity class : 

@Entity
@IdClass(ERHDSTV1Id.class)
@Table(name = "ERHDSTV1")
@Data
public class ERHDSTV1 {
   @Id
   @Column(name = "DFBOGARM")
   private String yearMonth;
   @Id
   @Column(name = "DFSALVAR")
   private Long salesType;
   @Column(name = "KVANTUM1")
   private Long volumeOne;
   @Column(name = "DSTOMK")
   private Long distributionCosts;
   @Column(name = "DSTMDN")
   private Long distributionMedian;
   @Column(name = "KVANTUMAKK")
   private Long volumeAccumulated;
   @Column(name = "DSTAKK")
   private Long distributionAccumulated;
   @Column(name = "DSTMNDAKK")
   private Long distributionMedianAccumulated;
}

The query from error message executes perfectly, when executed from an sql tool.

and headers are: DFBOGARM, KVANTUM1, DSTOMK, DSTAKK

Upvotes: 4

Views: 1533

Answers (1)

Anders Metnik
Anders Metnik

Reputation: 6237

Okay, so the error was that I have to select values for every column above.

which means i made the query like this: 

@Query(value = "select  "
        + "    DFBOGARM, 1 as DFSALVAR, 1 as DSTMND, 1 as DSTMNDAKK, 1 as KVANTUMAKK, "
        + "    sum(KVANTUM1) as KVANTUM1, "
        + "    (sum(DSTOMK) * 1000 / sum(KVANTUM1)) as DSTOMK, "
        + " (sum(DSTAKK) * 1000 / sum(KVANTUMAKK)) as DSTAKK "
        + "from NHODATA.ERHDSTV1 "
        + "where DFSALVAR in(52, 55) "
        + "group by DFBOGARM "
        + "order by DFBOGARM", nativeQuery = true)

and that worked.

Upvotes: 4

Related Questions