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

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 Error: ORA-00904: “DAYS_EXPERIENCE”: invalid identifier 00904. 00000 – “%s: invalid identifier”
*Cause:
*Action:

解决方法

根据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')
  ) < 730;
SELECT COUNT(*) FROM student_experience;

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!