Oracle SQL Rewrite so field does not return invalid indentifier

user_16353056 0
Viewed 24

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

oracle sql
2 Answers

Would this modification of your "original" query do?

SQL> select r.crn,r.term, c.term_descrip from registered r
  2  left join course c on c.crn =r.crn
  3  where c.term = (select max(c1.term)
  4                  from course c1
  5                 where c.crn = c1.crn
  6                   and c1.term <= r.term);

       CRN TERM   TERM_DESCRIP
---------- ------ -------------------------
       123 202101 Physics
       456 202001 Algebra
       789 202101 Gym
       123 202001 Anatomy
       456 201905 Algebra
       789 202101 Gym

6 rows selected.

SQL>

What's being changed? AND switched to WHERE :)

user_90979060

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

user_27787100
Related