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

Oracle SQL - Help using Case in a Select Statement

提问by AME

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; 

Any idea why I keep getting this error: Error report:

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

SQL Error: ORA-00904: "DAYS_EXPERIENCE": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action:

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

回答by The Scrum Meister

You cannot reference a alias in the WHEREclause. Either use a subquery, or better just the entire CASE...ENDinto your where clause.

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

Updated query per OP's comments:

根据 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; 

回答by RichardTheKiwi

The below is a direct simplification of the query in the question, taking MAX (any row) against MIN (any row). The Scrum Meister's answerfixes the OP's logic as well, to correctly cater for gaps between jobs.

下面是问题中查询的直接简化，取 MAX（任何行）对 MIN（任何行）。Scrum Meister 的回答也修正了 OP 的逻辑，以正确满足工作之间的差距。

---

---

这应该就是你所需要的。让 student_schools 表 JOINed 似乎没有任何价值，除非存在 position_rd 记录没有student_schoolsstudent_schools记录的情况。

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 

• The NVL takes care of replacing a non-existent end_date with SYSDATE

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

If you do need to validate student_schools- just add an INNER JOIN to it. Nowhere else is it needed.

如果您确实需要验证student_schools- 只需添加一个 INNER JOIN 即可。没有其他地方需要它。

回答by MinhD

For short and more readable code, use an outer SELECT:

对于简短且更具可读性的代码，请使用外部 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;

回答by Anil Soman

you cannot use guiven name of the field directly in where clause. You need to repeat the logic in where clause

您不能在 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;