如何在JPQL中编写此SQL select？

Question

我有一张桌子，叫它REI_BODY，还有一张，叫它REI_AMT_INFO。我在oracle /sql中有一个选择

选择*， (从REI_AMT_INFO I中选择I.REI_ID=T.REI_ID和AMT_TYPE_CD = '01') AMT_01，(从REI_AMT_INFO I I.REI_ID=T.REI_ID和AMT_TYPE_CD = '02') AMT_02中选择AMT，从I.REI_ID=T.REI_ID和AMT_TYPE_CD =‘01’的REI_AMT_INFO I中选择SGN_CD ) SGN_01，(从SGN_CD I选择I.REI_ID=T.REI_ID和AMT_TYPE_CD = '02') SGN_02，从REI_BODY T

这是REI_AMT_INFO表，没有外键！

REI_ID (Number);
AMT_TYPE_CD (Varchar);
AMT (Number);
SGN_CD (Varchar);

我必须用JPQL语言创建这个select。我怎么能这么做？

Answer 1

@NamedQuery(
    name="REI_BODY",
    query="SELECT *,

  (SELECT AMT    FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '01') AMT_01,
  (SELECT AMT    FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '02') AMT_02,

  (SELECT SGN_CD FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '01') SGN_01,
  (SELECT SGN_CD FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '02') SGN_02,

FROM REI_BODY T"
)

检查链接