Hi I have a query on QTV,
create or replace procedure test11(ename_in VARCHAR(55)
AS
DECLARE
q_eids QUERY(eid INTEGER)=select employee_id FROM EMPLOYEE where name=ename;
/*
alternative way if we wish to go in iterative way
v_eids ARRAY(RECORD(eid INTEGER)) = collect(q_eids);
the above captures the the results in an array of records but not array. Which I can’t pass in the where clause of the query.
*/
BEGIN
q_out QUERY(eid INTEGER
ename VARCHAR(55),
deptid INTEGER,
dept_name VARCHAR(55)
) = SELECT e.eid,e.ename,e.depid,d.dept_name
FROM employee e inner join dept d on (e.deptid=d.deptid)
WHERE eid in (select eid from q_eids)
…
…
there are multiple queries like this.
using (select eid from q_eids)
END;
whenever QTV calls/ collect is used the actual query will be triggered. instead I would like to execute the actual query once and only once, persists the results and use them.
The output persisted in that should be used across all the queries where the QTV is called.
Could you please suggest me is there any ways that Singlestore provides( not allowed to use TEMP Tables).
With regards,
Prathap Reddy G