crm@zhang>select t1.col1, t1.col2, decode(t1.col3, 1, t2.col2, null) from 2 (select col1, col2, row_number() over(partition by col1 order by col2) col3 from ta) t1, 3 tb t2 4 where t1.col1=t2.col1(+); COL1 COL2 DECODE(T1.COL3,1,T2. ---------- -------------------- -------------------- 1 a e 1 b 1 c 2 d f 2 d
crm@zhang>select t1.col1, t1.col2, decode(t1.col3, 1, t2.col2, 'null') from 2 (select col1, col2, row_number() over(partition by col1 order by col2) col3 from ta) t1, 3 tb t2 4 where t1.col1=t2.col1(+); COL1 COL2 DECODE(T1.COL3,1,T2. ---------- -------------------- -------------------- 1 a e 1 b null 1 c null 2 d f 2 d null
2 (select col1, col2, row_number() over(partition by col1 order by col2) col3 from ta) t1,
3 tb t2
4 where t1.col1=t2.col1(+); COL1 COL2 DECODE(T1.COL3,1,T2.
---------- -------------------- --------------------
1 a e
1 b
1 c
2 d f
2 d
2 (select col1, col2, row_number() over(partition by col1 order by col2) col3 from ta) t1,
3 tb t2
4 where t1.col1=t2.col1(+); COL1 COL2 DECODE(T1.COL3,1,T2.
---------- -------------------- --------------------
1 a e
1 b null
1 c null
2 d f
2 d null