java - 在AS400 db2上使用JPA的“未知列”

我收到以下错误:

无法执行查询; SQL [选择DFBOGARM，总和(KVANTUM1)
作为KVANTUM1，(sum(DSTOMK)* 1000 / sum(KVANTUM1))作为DSTOMK，
(总和(DSTAKK)* 1000 /总和(KVANTUMAKK))作为DSTAKK从
NHODATA.ERHDSTV1，其中(52，55)中的DFSALVAR由DFBOGARM排序
DFBOGARM];嵌套异常为
org.hibernate.exception.SQLGrammarException:无法执行查询

引起原因:org.hibernate.exception.SQLGrammarException:无法
执行查询

引起原因:com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException:
检测到未定义的列名。

查询实现:

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
@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;
}

从sql工具执行时，错误消息查询可以完美执行。

标题为:DFBOGARM，KVANTUM1，DSTOMK，DSTAKK

最佳答案

好的，所以错误是我必须为上面的每一列选择值。

这意味着我这样查询:

@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)

并且有效。