Oracle SQL - Case-sensitive help in a select expression

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; 

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

SQL error: ORA-00904: "DAYS_EXPERIENCE": invalid identifier 00904. 00000 - "% s: invalid identifier" * Reason:
* Action:

Answer 1

You cannot reference an alias in a WHERE . Either use a subquery, or best of all CASE...END in a where clause.

Updated request for OP comments:

 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

The following is a direct simplification of the query in question, taking MAX (any row) versus MIN (any row). Scrum Meister's answer also corrects the OP logic to properly serve spaces between jobs.

---

That should be all you need. Having a student_schools JOINed table does not seem to add value unless there are cases where position_rd records exist without student_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 

• NVL will take care of replacing nonexistent end_date with SYSDATE

If you need to check student_schools , just add an INNER JOIN to it. Nowhere else is needed.

Answer 3

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

 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

For shorter and more readable code, use an external 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;