CODEWITHSUNDEEP
sqloracle10g
03Usr
03Usr

Reputation: 3435

How would I store the result of a select statement so that I can reuse the results to join to different tables?

How would I store the result of a select statement so that I can reuse the results to join to different tables? This will also be inside a cursor.

Below is some pseudo code, in this example I have kept the Select statement simple but in real life it is a long query with multiple joins, I have to use the identical SQL twice to join to 2 different tables and as it is quite long and can be changed in the future hence I want to be able reuse it.

I have tried creating a view and storing the results of the select statement in it but it seems I can't create a view inside the cursor loop, when I tried I am getting "Encountered the symbol "CREATE"" error. 

DECLARE TYPE cur_type IS REF CURSOR;
CURSOR PT_Cursor IS

    SELECT * FROM Table1

    PT_Cursor_Row   PT_Cursor%ROWTYPE;

BEGIN
 OPEN PT_Cursor;

 LOOP
    FETCH PT_Cursor INTO PT_Cursor_Row;
    EXIT WHEN PT_Cursor%NOTFOUND;

    Select ID From Table2 --this is actually a long complext query
        INNER JOIN Table3 ON Table2.ID = Table3.ID
    WHERE Table2.ID = PT_Cursor_Row.ID

    Select * From Table2 --this is actually a long complext query
        LEFT JOIN Table4 ON Table2.ID = Table4.ID
    WHERE Table2.ID = PT_Cursor_Row.ID                               

END LOOP;

 CLOSE PT_Cursor;
END;

Upvotes: 0

Views: 1820

Answers (2)

EvilTeach
EvilTeach

Reputation: 28837

Temp tables certainly are a viable option. One can also use the with statement to 'reuse' results sets.

WITH 
PEOPLE AS 
(
    SELECT 'FRED'   NAME, 12 SHOE_SIZE FROM DUAL UNION ALL
    SELECT 'WILMA'  NAME,  4 SHOE_SIZE FROM DUAL UNION ALL
    SELECT 'BARNEY' NAME, 10 SHOE_SIZE FROM DUAL UNION ALL
    SELECT 'BETTY'  NAME,  3 SHOE_SIZE FROM DUAL
),
WOMAN AS
(
    SELECT 'BETTY'  NAME FROM DUAL UNION ALL
    SELECT 'WILMA'  NAME FROM DUAL
)
SELECT 'WOMANS ', PEOPLE.NAME, PEOPLE.SHOE_SIZE
FROM PEOPLE, WOMAN
WHERE PEOPLE.NAME = WOMAN.NAME

UNION ALL

SELECT 'MENS   ', PEOPLE.NAME, PEOPLE.SHOE_SIZE
FROM PEOPLE, WOMAN
WHERE PEOPLE.NAME = WOMAN.NAME(+) 
  AND WOMAN.NAME IS NULL

Upvotes: 2

user359040
user359040

Reputation:

One way to save the results from a query is via a temporary table - there's a short answer to this question that describes how to create them, while there is a longer answer here that discusses how to use them, with possible alternatives.

Upvotes: 3

Related Questions