how will get subquery return multiple values and passing to the main query
Hi all ;
here i given one sql query
1)select decode(a.FLG,'Y','yes','N','no','null')||'] '||a.p_type||' : '|| initcap(replace(substr(b.mgr,0,instr(b.mgr,'@')-1),'.',' '))||' - '||
b.name||' ('|| substr(a.name,0,instr(a.name,'-')-1)||')'
from table1 a
join table3 c on c.emptype = a.emptype
left outer join table2 b on a.name = b.name
where a.mgrid = 100;
if i run this above query returning multiple values depend on sa.mgr values.
like output coming like this
-------------------------------------------------------------------------------
[yes]:2000-anbarasan
[yes]:2700-anb
[yes]:2000-rasan
[yes]:2807-anbarasan
[yes]:2700-anbanu
[yes]:2000-null
2) this sub query i am passing with main query
select sa.mgrid,sa.sal,(select decode(a.FLG,'Y','yes','N','no','null')||'] '||a.p_type||' : '||
initcap(replace(substr(b.mgr,0,instr(b.mgr,'@')-1),'.',' '))||' - '||
b .name||' ('|| substr(a.name,0,instr(a.name,'-')-1)||')'
from table1 a
join table3 c on c.emptype = a.emptype
left outer join table2 b on a.name = b.name
where a.mgrid = 100)" test " from table4 sa,table5 te ,table6 ft where sa.id(+)=te.id and sa.mgr=ft.mgr;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
my final out put required like this:
--------------------------------------------------------------------------------------------------------------------------------------------------
mgrid sal test
--------------------------------------------------------------------------------------------------------------------------------------------------
100 20000 [yes]:2000-anbarasan
[yes]:2700-anb
[yes]:2000-rasan
[yes]:2807-anbarasan
[yes]:2700-anbanu
[yes]:2000-null
--------------------------------------------------------------------------------------------------------------------------------------------------
but i am getting erro:
1)missing paranths-it solved
2)single row subquery return more then one row.
give me the correct solution.how will solve this problem
Edited by: anbarasan on Sep 29, 2008 6:49 AM
Edited by: anbarasan on Sep 29, 2008 6:51 AM
Edited by: anbarasan on Sep 29, 2008 6:53 AM
Edited by: anbarasan on Sep 29, 2008 6:56 AM
Edited by: anbarasan on Sep 29, 2008 6:58 AM