Oracle SQL - Help using Case in a Select Statement

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" *Cause:
*Action:

The Scrum Meister · Accepted Answer · 2011-02-17T08:03:55.327

10

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

Updated query per OP's 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;

edited Feb 17 '11 at 08:03

answered Feb 17 '11 at 04:15

The Scrum Meister

29,681
8
66
64

With your code, I get an error that points to the WHERE clause: SQL Error: ORA-00934: group function is not allowed here 00934. 00000 - "group function is not allowed here" – AME Feb 17 '11 at 04:24
1

@ame Since it uses aggregate functions, it belongs in the `HAVING` clause. Updated my code. – The Scrum Meister Feb 17 '11 at 04:25
hmmm... I still get the same error, even with the updated code. SQL Error: ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" – AME Feb 17 '11 at 04:28
1

@ame The `CASE` includes both single and aggregate fields, If you can please post your table definition, sample data & what you want the results to look like we can better help you! – The Scrum Meister Feb 17 '11 at 04:34
The student_schools table has student ids and the position_rd table has jobs (so there are multiple jobs per student id). I want to create a table with 2 fields: field 1 - distinct student id, field 2 - total number of days employed. Then, I'd like to exclude students with over 2 years of work experience (730 days). The end result is a table with student ids who have less than 730 days of experience. – AME Feb 17 '11 at 04:44
1

@ame I Updated the query. Note 1) Removed the `DISTINCT` since you have the `GROUP BY` 2) Simplified the `CASE`. – The Scrum Meister Feb 17 '11 at 04:49
now I get the following error: SQL Error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER... – AME Feb 17 '11 at 04:57
Does the CASE expression in SELECT (non-aggregate) actually work? – RichardTheKiwi Feb 17 '11 at 08:00
@richard-aka-cyberkiwi No. good point. Updated. Answer was brain compiled with help from Notepad++ – The Scrum Meister Feb 17 '11 at 08:03
But it was accepted - go figure - so I asked. But I get you now about the gaps. I was literally fixing the OP's query (max against min). – RichardTheKiwi Feb 17 '11 at 08:40

score 2 · Answer 2 · edited May 23 '17 at 10:34

2

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

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

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

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

edited May 23 '17 at 10:34

Community

1
1

answered Feb 17 '11 at 06:20

RichardTheKiwi

105,798
26
196
262

This will return incorrect results if there are days in between job rows. – The Scrum Meister Feb 17 '11 at 07:55
@richard-aka-cyberkiwi If a user has 2 rows: 1) 1/1/2011 to 2/1/2011 and 2) 2/10/2011 to 2/20/2011. Your query will return `51` days experience. instead of `41`. That is why a `SUM()` on the individual rows is needed (see my answer). And i think just "cyberwiki" is better – The Scrum Meister Feb 17 '11 at 08:00
@The SM - the MAX and MIN are taken independently. so it will take 2/20 against 1/1. – RichardTheKiwi Feb 17 '11 at 08:42
@richard-aka-cyberkiwi Ya which will output `51`. The OP wants (look in the comments to my answer) the output to be `41`. – The Scrum Meister Feb 17 '11 at 08:51

score 0 · Answer 3 · answered Jun 06 '14 at 02:49

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

score 0 · Answer 4 · answered Feb 17 '11 at 04:32

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

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;

Oracle SQL - Help using Case in a Select Statement

4 Answers4