嵌套事例查询Db2

kq0g1dla 于 2022-11-07 发布在 DB2

关注(0)|答案(2)|浏览(188)

我试图在DB2中创建一个嵌套的case语句，但它抛出了一个错误。

select tmp.claim_number,
       SUM(tmp.LUMP_SUM_E) as LUMP_SUM_E
  from (
select c.claim_number,
       p.amount,
       p.effective_gross_amt,
       --p.lump_sum_e,
       p.released,
       p.to,
       p.payment_option,
       p.sub_benefit_type,
       p.PAYMENT_FIELD,
       CASE WHEN ((EXTRACT(YEAR FROM p.to) < EXTRACT(YEAR FROM p.released)) 
       THEN
          CASE WHEN (p.to <= '2020-07-01' and p.amount > 0)
          THEN amount ELSE 0 
          END  
       END AS lump_sum_e   
from CLAIMS.claim c inner join claims.payment p on c.id = p.claim_id and c.claim_Number IN (75248)
AND (
      (p.RELEASED >= '2020-07-01' AND p.RELEASED <= '2021-06-30') 
   OR 
      (p.ATO_RELEASE_DATE >= '2020-07-01' AND p.ATO_RELEASE_DATE <= '2021-06-30')
    )
  ) tmp
GROUP by tmp.claim_number;

它在扔

1) [Code: -104, SQL State: 42601]  An unexpected token "THEN" was found following "FROM p.released))  
".  Expected tokens may include:  ")".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.22.29
2) [Code: -727, SQL State: 56098]  An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-104", SQLSTATE "42601" and message tokens "THEN|FROM p.released))  
|)".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.22.29

你知道如何在Db2中写一个嵌套的case语句吗？
此致，R

db2

来源：https://stackoverflow.com/questions/72269520/nested-case-query-db2

2条答案

按热度按时间

wz1wpwve1#

您需要修正查询语法。

select tmp.claim_number,
       SUM(tmp.LUMP_SUM_E) as LUMP_SUM_E
  from (select c.claim_number,
               p.amount,
               p.effective_gross_amt,
               --p.lump_sum_e,
               p.released,
               p.to,
               p.payment_option,
               p.sub_benefit_type,
               p.PAYMENT_FIELD,
               CASE WHEN (EXTRACT(YEAR FROM p.to) < EXTRACT(YEAR FROM p.released)) 
                         THEN
                             CASE WHEN (p.to <= '2020-07-01' and p.amount > 0)
                                       THEN amount ELSE 0 
                             END  
               END AS lump_sum_e   
          from CLAIMS.claim c 
         inner join claims.payment p on c.id = p.claim_id 
                                    and c.claim_Number IN (75248)
                                    AND (
                                         (p.RELEASED >= '2020-07-01'
                                          AND p.RELEASED <= '2021-06-30') 
                                         OR 
                                         (p.ATO_RELEASE_DATE >= '2020-07-01'
                                          AND p.ATO_RELEASE_DATE <= '2021-06-30')
                                        )
      ) tmp
GROUP by tmp.claim_number;

赞(0）回复(0）举报 2022-11-07

db2dz4w82#

请考虑在内部CASE表达式两边使用显式括号。还要确保(和)同步。根据CASE statement的语法，它应该可以工作。

赞(0）回复(0）举报 2022-11-07