Oracle SQL Rewrite so field does not return invalid indentifier

I am creating a view and you cannot create a view with a column outer-joined to a subquery, so I found another way to do it, but now I am having an issue with a field being out of scope.

create table registered (
crn number,
term varchar2(6));

create table course(
crn number,
term varchar2(6),
term_descrip varchar2(25));


insert into registered values (123,'202101');
insert into registered values (456,'202001');
insert into registered values (789,'202101');
insert into registered values (123,'202001');
insert into registered values (456,'201905');
insert into registered values (789,'202101');
insert into registered values (246,'202101');

insert into course values (123,'202001','Anatomy');
insert into course values (123,'202101','Physics');
insert into course values (456,'200001','English');
insert into course values (456,'201901','Algebra');
insert into course values (789,'199901','Gym');

Original Query

select r.crn,r.term, c.term_descrip from registered r
left join course c on c.crn =r.crn and c.term = 
(select max(c1.term) from course c1 where c.crn = c1.crn and
 c1.term <= r.term);

Results

crn	term	descrip
123	202101	Physics
456	202001	Algebra
789	202101	Gym
123	202001	Anatomy
456	201905	Algebra
789	202101	Gym
246	202101	null

New query

select r.crn,r.term, max_term.term_descrip from registered r
 left join (select * from course c where 
            c.term = (select max(c1.term) from course c1 
                      where c.crn = c1.crn and c1.term <= r.term)) max_term
            on max_term.crn = r.crn

r.term gets invalid identifier since it is out of scope, how can I rewrite to get it into scope? Any suggestions are greatly appreciated

Answer

If you have only one row for each combination of crn and term in course table, then you may consider lateral join:

select r.crn, r.term, c.term_descrip
from registered r 
  outer apply(
    select *
    from course c
    where c.crn = r.crn
      and c.term <= r.term
    order by c.term desc
    fetch first 1 rows only
  ) c

CRN | TERM   | TERM_DESCRIP
--: | :----- | :-----------
123 | 202101 | Physics     
456 | 202001 | Algebra     
789 | 202101 | Gym         
123 | 202001 | Anatomy     
456 | 201905 | Algebra     
789 | 202101 | Gym         
246 | 202101 | null

db<>fiddle here

Advertisement

Answer