select a.column1,
a.column2,
decode(a.column1,'ABC', (select service1 from Table1 b where a.column1= b.column1),
decode((select count(*) from Table3 c where a.column1= c.column1) , 1, 'CDF',
decode((select count(*) from Table4 d where a.column1= d.column1) , 1, 'CDF',
decode((select count(*) from Table5 d where a.column1 = d.column1) , 1, 'CDF', '')) )
) as service,
from
TEST a