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:
解决方法
您不能在WHERE子句中引用别名.要么使用子查询,要么更好地使用整个CASE … END到where子句中.
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;
