sql>select * from data_table;
 
       ID        RT        RC
--------- --------- ---------
        1         1         1
        2         1         1
        3         1         2
        4         1         3
        5         1
        6         2
 
6 rows selected.
 
sql>select * from translation_table;
 
       RT        RC T
--------- --------- -
        1         1 A
        1         2 B
        1           C
 
3 rows selected.
 
sql>select id, rt, rc,
  2         nvl((select trans_code
  3                from translation_table tt
  4               where tt.rt = dt.rt
  5                 and tt.rc = dt.rc), nvl((select trans_code
  6                                            from translation_table tt
  7                                           where tt.rt = dt.rt
  8                                             and tt.rc is null), 'Failed to translate')) trans_code  
  9    from data_table dt
 10   order by id;
 
       ID        RT        RC TRANS_CODE
--------- --------- --------- -------------------
        1         1         1 A
        2         1         1 A
        3         1         2 B
        4         1         3 C
        5         1           C
        6         2           Failed to translate
 
6 rows selected.