Oracle SQL - 帮助在 Select 语句中使用 Case

Question

CREATE TABLE student_totalexp2 nologging compress AS
SELECT /*+parallel(a,4)*/ DISTINCT a.member_sk, 
       CASE 
         WHEN b.end_date IS NULL THEN 
           SYSDATE - MIN(TO_DATE(b.start_date,'yyyymm'))
         ELSE 
           (MAX(TO_DATE(b.end_date,'yyyymm')) - MIN(TO_DATE(b.start_date,'yyyymm')))  
       END as days_experience
  FROM student_schools a 
  JOIN rdorwart.position_rd b ON a.member_sk = b.member_sk 
 WHERE days_experience < 730 
 GROUP BY a.member_sk;

SELECT COUNT(*) 
  FROM student_experience; 

知道为什么我不断收到此错误：错误报告：

SQL 错误：ORA-00904：“DAYS_EXPERIENCE”：无效标识符 00904。00000 -“%s：无效标识符” *原因：
*行动：

Answer 1

您不能在 WHERE 子句中引用别名。要么使用子查询，要么最好将整个 CASE...END 放入 where 子句中。

根据OP的评论更新查询：

create table student_totalexp2 nologging compress as 
SELECT a.member_sk, 
 SUM(CASE WHEN b.end_date IS NULL
    THEN sysdate 
    ELSE to_date(b.end_date,'yyyymm') 
  END - to_date(b.start_date,'yyyymm')) as days_experience
FROM student_schools a INNER JOIN rdorwart.position_rd b 
  ON a.member_sk = b.member_sk 
GROUP BY a.member_sk
HAVING SUM(
  CASE WHEN b.end_date IS NULL
    THEN sysdate 
    ELSE to_date(b.end_date,'yyyymm') 
  END - to_date(b.start_date,'yyyymm')
  ) < 730;
SELECT COUNT(*) FROM student_experience; 

Answer 2

下面是问题中查询的直接简化，将 MAX（任何行）与 MIN（任何行）进行比较。 Scrum Meister 的答案修复了OP 的逻辑也是如此，以正确地满足工作之间的差距。

---

This should be all you need. Having the student_schools table JOINed seems to add no value, unless there are cases where position_rd records exist without a student_schools record.

CREATE TABLE student_totalexp2 nologging compress AS
SELECT b.member_sk, 
    NVL(MAX(TO_DATE(b.end_date,'yyyymm')), SYSDATE)
      - MIN(TO_DATE(b.start_date,'yyyymm')) as days_experience
FROM rdorwart.position_rd b
GROUP BY b.member_sk
HAVING NVL(MAX(TO_DATE(b.end_date,'yyyymm')), SYSDATE)
         - MIN(TO_DATE(b.start_date,'yyyymm')) < 730 

• NVL 负责用 SYSDATE 替换不存在的 end_date

如果您确实需要验证 student_schools - 只需向其添加 INNER JOIN 即可。其他地方都不需要它。

Answer 3

您不能直接在 where 子句中使用字段的 guiven 名称。您需要重复where子句中的逻辑

select /*+parallel(a,4)*/ distinct a.member_sk,  
CASE WHEN b.end_date is null 
THEN sysdate - min(to_date(b.start_date,'yyyymm'))  
ELSE (max(to_date(b.end_date,'yyyymm')) - min(to_date(b.start_date,'yyyymm'))) 
END as days_experience       
from student_schools a INNER JOIN rdorwart.position_rd b        
ON a.member_sk = b.member_sk          
where (CASE WHEN b.end_date is null 
       THEN sysdate - min(to_date(b.start_date,'yyyymm'))  
       ELSE (max(to_date(b.end_date,'yyyymm')) - min(to_date(b.start_date,'yyyymm'))) 
       END) < 730          
group by a.member_sk;     
select count(*) from student_experience;

Answer 4

对于简短且更具可读性的代码，请使用外部 SELECT：

CREATE TABLE student_totalexp2 nologging compress AS
SELECT member_sk, days_experience FROM (
    SELECT a.member_sk
         , SUM(CASE WHEN b.end_date IS NULL 
               THEN sysdate ELSE to_date(b.end_date,'yyyymm') END
               - to_date(b.start_date,'yyyymm')) AS days_experience
    FROM student_schools a
    INNER JOIN rdorwart.position_rd b ON a.member_sk = b.member_sk
    GROUP BY a.member_sk)
WHERE days_experience < 730;


SELECT COUNT(*)
FROM student_experience;