How can I reference first table from second table in an Oracle WITH clause? CTE

Question

I read here:

Can we have multiple "WITH AS" in single sql - Oracle SQL

that if I have 2 tables in my WITH clause, I can reference the first tables in the my second table's where clause. However, I have had no luck doing this in practice. Any insights would be appreciate.

I've tried every iteration of aliases that I can think of when trying to access the first table.

with term as (
select course_reference_number, academic_period  from student_course ),
    term2 as (
select subject_desc, academic_period from student_course where 
and term.course_reference_number = 22638) 

select term.*, term2.* 
from term, term2 
where term.academic_period= term2.academic_period;

I get the error: ORA-00904: "TERM"."COURSE_REFERENCE_NUMBER": invalid identifier

Please explain what you are trying to do. Your query doesn't make sense. — Gordon Linoff, Aug 28 '19 at 16:37

score 2 · Answer 1 · answered Aug 28 '19 at 17:52

You are using the term.course_reference_number in query of term2 without using term in from clause.

Please note that term is not in the scope of term2 untill it is used in from clause of term2.

You can achieve the desired result by moving WHERE clause of term2 to the final query as following:

with term as (
select course_reference_number, academic_period  from student_course ),

    term2 as (
select subject_desc, academic_period from student_course) 

select term.*, term2.* 
from term join term2 
On (term.academic_period= term2.academic_period)
Where term.course_reference_number = 22638;

Suggestion: always use ansi standard joins.

Cheers!!

score 2 · Answer 2 · answered Aug 28 '19 at 17:53

Yes, you can reference the first CTE in the second one, but - you have to do it properly. I don't know what you want to do, but this example shows how you might have done it.

Test case first:

SQL> create table student_course
  2    (course_Reference_Number number,
  3     academic_period         number,
  4     subject_desc            varchar2(20));

Table created.

SQL>
SQL> insert into student_Course values (1, 100, 'Math');

1 row created.

SQL> insert into student_Course values (2, 200, 'Science');

1 row created.

SQL> insert into student_Course values (22638, 300, 'Biology');

1 row created.

Your query:

SQL> with
  2    term as
  3      (select s.course_Reference_number,
  4              s.academic_period
  5       from student_course s
  6      ),
  7    term2 as
  8      (select s.subject_desc,
  9              s.academic_period
 10       from student_course s join
 11            term t                        --> yes, you can reference the first CTE
 12            on t.academic_period = s.academic_period
 13       where t.course_Reference_number = 22638
 14      )
 15  select t1.*,
 16         t2.*
 17  from term t1 join term2 t2 on t1.academic_period = t2.academic_period;

COURSE_REFERENCE_NUMBER ACADEMIC_PERIOD SUBJECT_DESC         ACADEMIC_PERIOD
----------------------- --------------- -------------------- ---------------
                  22638             300 Biology                          300

SQL>

score 0 · Answer 3 · answered Aug 28 '19 at 16:35

You have some typos in your query.

You have an extra and in for starters. Try this:

with 

term as ( 
    select course_reference_number, academic_period 
    from student_course ), 
term2 as ( 
    select subject_desc, academic_period 
    from student_course 
    where term.course_reference_number = 22638)

select term.*, term2.* 
from term, term2 
where term.academic_period = term2.academic_period;

How can I reference first table from second table in an Oracle WITH clause? CTE

3 Answers3