Cursor with multiple queries in oracle not compiling

Question

I have created a cursor that has two queries joined with inner join, but query is not compiling their is error at the end of first query but the same query is getting executed without cursor.

cursor data is
select * from
select rid,id, order from table1
inner join
select pid, name, order from table2
on table1.order =  table2.order

original query is much bigger and complicated but end result would be this.

Their are compilation errors at the end of first query and those are generic nature, I guess may be syntax for creating a two joined queries is wrong (this is a wild guess though)

Error:

SQL statement ignored //at select word of first query

Missing right parenthesis //at the last word of first query

Answer 1

Example based on Scott's schema:

• SELECT should contain column aliases if columns returned by those inline views share the same name; otherwise, you won't know which one you're using
• inline views should have their own aliases; basically, that's always a good idea - prefix columns with table aliases, otherwise you'll soon forget which column belongs to which table

---

SQL> declare
  2    cursor data is
  3      select a.empno a_empno, b.ename b_ename
  4      from (select empno, ename, deptno from emp) a
  5            inner join
  6           (select empno, ename, deptno from emp) b
  7            on a.deptno = b.deptno
  8      where rownum < 5;
  9  begin
 10    for data_r in data loop
 11      dbms_output.put_line(data_r.b_ename);
 12    end loop;
 13  end;
 14  /
SMITH
JONES
SCOTT
ADAMS

PL/SQL procedure successfully completed.

SQL>