试图解决这个问题 - 发现")"(在字符 81)期望一个关键字 - 错误

Question

我正在尝试运行这个，但我不断得到，错误

^ found ")" (at char 81) expecting a keyword

字符 81 位于第 2 个选择部分 AS

那里显然有一个关键字 AS

SELECT full_name, clm_id, 
CASE WHEN OPEN_DAYS BETWEEN 0 AND 30 THEN 1 ELSE 0 END) AS '0TO30' 
     WHEN OPEN_DAYS BETWEEN 31 AND 60 THEN 1 ELSE 0 END) AS '31TO60' 
     WHEN OPEN_DAYS BETWEEN 0 AND 30 THEN 1 ELSE 0 END) AS '61TO90' 
     WHEN OPEN_DAYS BETWEEN 0 AND 30 THEN 1 ELSE 0 END) AS '91TO120' 
     WHEN OPEN_DAYS >= 120 THEN 1 ELSE 0 END) AS 'GreaterThan120' 
     ELSE 'OTHERS' 
END AS DAYS_RANGE, 
COUNT(*) as NUMBER_OF_DAYS 
FROM 
    (
    SELECT DATE(CURRENT_DATE) - DATE(RCVD_DT) AS Open_Days 
        FROM clm_claim_rds_vw 
        WHERE CLM_ID like 'CC%' 
        AND EXTNL_STAT_CD = 'SUBMITTED'
    ) A 
LEFT JOIN mt_employee_current_vw  
    ON clm_claim_rds_vw.examined_by_empl_id = mt_employee_current_vw.employee_number 
GROUP BY full_name

Answer 1

你在每个 END 之后都有错误的关闭。 尝试删除

SELECT full_name, clm_id, 
CASE WHEN OPEN_DAYS BETWEEN 0 AND 30 THEN 1 ELSE 0 END AS '0TO30' 
   WHEN OPEN_DAYS BETWEEN 31 AND 60 THEN 1 ELSE 0 END AS '31TO60' 
   WHEN OPEN_DAYS BETWEEN 0 AND 30 THEN 1 ELSE 0 END AS '61TO90' 
   WHEN OPEN_DAYS BETWEEN 0 AND 30 THEN 1 ELSE 0 END AS '91TO120' 
   WHEN OPEN_DAYS >= 120 THEN 1 ELSE 0 END AS 'GreaterThan120' 
   ELSE 'OTHERS' 
END AS DAYS_RANGE, 
COUNT(*) as NUMBER_OF_DAYS 
FROM 
  (
  SELECT DATE(CURRENT_DATE) - DATE(RCVD_DT) AS Open_Days 
    FROM clm_claim_rds_vw 
    WHERE CLM_ID like 'CC%' 
    AND EXTNL_STAT_CD = 'SUBMITTED'
  ) A 
LEFT JOIN mt_employee_current_vw  
  ON clm_claim_rds_vw.examined_by_empl_id = mt_employee_current_vw.employee_number 
GROUP BY full_name

Answer 2

我认为你想要条件聚合：

SELECT ec.full_name, ec.clm_id, 
       SUM(CASE WHEN OPEN_DAYS BETWEEN 0 AND 30 THEN 1 ELSE 0 END) AS cnt_0TO30, 
       SUM(CASE WHEN OPEN_DAYS BETWEEN 31 AND 60 THEN 1 ELSE 0 END) AS cnt_31TO60,
       SUM(CASE WHEN OPEN_DAYS BETWEEN 61 AND 90 THEN 1 ELSE 0 END) AS cnt_61TO90,
       SUM(CASE WHEN OPEN_DAYS BETWEEN 91 AND 120 THEN 1 ELSE 0 END) AS cnt_91TO120,
       SUM(CASE WHEN OPEN_DAYS >= 120 THEN 1 ELSE 0 END) AS cnt_GreaterThan120 
       SUM(CASE WHEN OPEN_DAYS IS NULL OR OPEN_DAYS < 0 THEN 1 ELSE 0 END) as cnt_Others,
       COUNT(*) as NUMBER_OF_DAYS 
FROM (SELECT DATE(CURRENT_DATE) - DATE(RCVD_DT) AS Open_Days 
      FROM clm_claim_rds_vw 
      WHERE CLM_ID like 'CC%' AND
            EXTNL_STAT_CD = 'SUBMITTED'
     ) ccr LEFT JOIN
     mt_employee_current_vw  ec
     ON ccw.examined_by_empl_id = ec.employee_number 
GROUP BY ec.full_name, ec.clm_id;