--> Function ' filter_id ' filters the ID's based on some conditions. --> Input is set of ID's. (Not directly taking the input since there is no provision to pass multiple rows to a function)
create function filter_id() return table (id bigint) begin --> Assuming input table is already created #temp_input_id retun query as select id from tbl a inner join #temp_input_id b on (a.id = b.id) where a.<conditions>; end; --> Calling Function: create function caller() return table (id bigint,col1 bigint, col2 bigint) begin --> do some processing --> Find out the input id's for filtering. --> Create temp table for providing input for the filtering function create temp table #TEMP1 as select id from tbla........; (Cannot move the input id logic to filter_function) --> calling the filter function create temp table #TEMP2 as select * from filter_id(); --> This is a generic function used in many functions. return query as select a.* from tb3 a inner join tb4 inner join tb 5 inner join #TEMP2; end; Is there any alternate way of achieving this? Passing multiple records to a function im creating a temp table before invoking the function. For receiving an output of multiple rows i'm creating a temp table to reuse further in the code. Can this be done using Refcursor? Is it possible to convert refcursor to a temp table and use it as normal table in query?
