How to reduce functions with simple select query?

[Rob Presto]

I have a function to identify the root parent of a particular id. I use this in function in select query,which invokes function for every row in table.I need to merge the function inside the query itself.Please suggest me.



Function

CREATE OR REPLACE FUNCTION fnroot(v_id int ) return int as

v_left int;

v_right int;

v-result int;



begin

select left,right into v_left,v_right from sam where id=v_id;

select id into v_result from sam

where id in (select id from mst m where m.depth=2 )

and left < v_left and right > v_right;

return v_result;

end



query:

select fnroot(s.id) from master s;