I have multiple select statements which is quite big. I will have to use the result sets to join in various cases . For the above problem statement can we collect the result in an array and join directly to tables ? If not please suggest other options.
Could you provide an example of what you are trying do? SQL allows you to use subqueries to join the result set of a SELECT to other tables.
From tab 1 join tab 2 on condition
Tab 3 join on condition
Followed by other complex logic.
Like wise i do have multiple logics like this which needs to joined with other table like below.
From table x join ( result of select statement )
My question is , if i collect records of complex select query in an array can i just use that array as table ? Like
CREATE PROCEDURE p() AS
qry QUERY(define columns) = complex select logic;
arr ARRAY(RECORD(define columns));
arr = COLLECT(qry);
From table x inner join arr on condition ;
I am trying to join table with array which has records from complex select query.
I do have lot of complex select queries in my procedure which needs to be joined with other table or i may also have to perform joins between results of two or more queries.
What could be the best solution for this ?