Hi!
In a package with many procedures, there is a big SELECT query that would be used as a subquery in many places.
What is the best way to not type it each time?
It is something like this:
SELECT a, b, c, nvl(t4.foo , t4.bar) as foobar, d , e
FROM TABLE1
INNER JOIN TABLE2 t2 USING (k)
INNER JOIN TABLE3 t3 USING (m)
CROSS JOIN TABLE4 t4
INNER JOIN TABLE5 ON TABLE5.cesId = t3.cesId
LEFT JOIN TABLE6 ON TABLE6.Id in (t4.foo , t4.bar)
where t4.foo is not null or t4.bar is not null
CONNECT BY prior t4.xerId = t4.cesId and prior t2.dog = t2.dog
START WITH t4.cesId = t3.cesId;
I thought about replacing it with a VIEW named big_one, so I could use it in other SQL like this:
SELECT HOUSES.* FROM HOUSES
JOIN big_one on big_one.a = HOUSES.x
WHERE HOUSES.size=2
AND big_one.b = 'whatever';
So what are my options?
- view?
- a function? Returning what? refcursor?
- copy the result of the big select into a table? Temporary table? (the data in it is updated like once a day, irregularly)
- store the big query as a text variable, an paste it to other queries and execute it as dynamic SQL with OPEN p_out FOR sql_string USIN paramter(s)... ?
Regards,
David