SQL> CREATE TABLE reftable (rno NUMBER, rname VARCHAR2(8), rcatno NUMBER);
  
Table created.
  
SQL> INSERT INTO reftable VALUES (1,'PENS',10);
SQL> INSERT INTO reftable VALUES (2,'PADS',20);
SQL> INSERT INTO reftable VALUES (3,'BOOKS',30);
SQL> INSERT INTO reftable VALUES (4,'PENCILS',40);
SQL> INSERT INTO reftable VALUES (6,'RUBBERS',60);
SQL> CREATE TABLE sourcetable (sno NUMBER, sname VARCHAR2(8), scatno NUMBER);
  
Table created.
  
SQL> INSERT INTO sourcetable VALUES (1,'RAM',10);
SQL> INSERT INTO sourcetable VALUES (2,'SHIRTS',21);
SQL> INSERT INTO sourcetable VALUES (3,'MOUSE',30);
SQL> INSERT INTO sourcetable VALUES (5,'TOYS',50);
SQL> INSERT INTO sourcetable VALUES (6,'RUBBERS',60);
SQL> COMMIT;
  
Commit complete.
  
SQL> SELECT s.sno
  2  ,      DECODE(NVL(r.rno,s.sno)
  3         ,      s.sno
  4         ,      DECODE(r.rcatno
  5                ,      s.scatno, r.rname
  6                ,      s.sname)) name
  7  ,      s.scatno              
  8  FROM   reftable     r
  9  ,      sourcetable  s
 10  WHERE  s.sno = r.rno (+)
 11  /
  
       SNO NAME         SCATNO
---------- -------- ----------
         1 PENS             10
         2 SHIRTS           21
         3 BOOKS            30
         5 TOYS             50
         6 RUBBERS          60
  
SQL>