CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Batman
Batman

Reputation: 6351

Store subquery results to be reused throughout query

I'm trying to re-use the results of a sub-query so I don't have to keep calling it through out my code, I tried: 

WITH MAX_VERSIONS AS (
    SELECT MAX(vernum) AS VERNUM, MAX(defvern) AS DEFVERN
    FROM   llattrdata, DTREE DT 
    WHERE  id = dt.dataid 
            AND defid = 3070056 
            AND attrid = 4
) 
SELECT ID
FROM   llattrdata ad, DTREE DT 
WHERE  ad.id = dt.dataid 
        AND ad.defid = 3070056 
        AND ad.attrid = 4
        AND ad.VERNUM = MAXVERSIONS.VERNUM
        AND ad.DEFVERN = MAXVERSIONS.DEFVERN;

Upvotes: 0

Views: 130

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Why would you go through all this trouble when you can use window functions:

SELECT ID
FROM (SELECT ID,
             RANK() OVER (PARTITION BY defid, attrid, ORDER BY versum, defvern DESC) as seqnum
      FROM llattrdata ad JOIN
           DTREE DT 
           ON ad.id = dt.dataid 
      WHERE ad.defid = 3070056 
     ) ad
WHERE seqnum = 1;

Window functions should have better performance than a JOIN and GROUP BY.

Upvotes: 0

John Woo
John Woo

Reputation: 263733

You can use CROSS JOIN since it only returns single row:

;WITH MAX_VERSIONS AS 
(
    SELECT MAX(vernum) AS VERNUM, MIN(defvern) AS DEFVERN
    FROM    llattrdata
            INNER JOIN DTREE DT
                ON id = dt.dataid 
    WHERE   defid = 3070056 
            AND attrid = 4
) 
SELECT  ID
FROM    llattrdata ad
        INNER JOIN DTREE DT 
            ON ad.id = dt.dataid 
        CROSS JOIN MAX_VERSIONS mv
WHERE   defid = 3070056 
        AND attrid = 4
        AND DT.VERNUM = mv.VERNUM    -- must also define which table the column
        AND DT.DEFVERN = mv.DEFVERN; -- is coming from to avoid ambiguity
                                     -- not sure whether it is from [ad] or [DT]

As an alternative, if you wanted to reuse the value over and over again without using a CTE, you can declare and store the value inside a variable:

DECLARE
    MAX_VERNUM NUMBER(10);
    MAX_DEFVERN  NUMBER(10);

SELECT  MAX(vernum), MIN(defvern)
        INTO MAX_VERNUM, MAX_DEFVERN
FROM    llattrdata
        INNER JOIN DTREE DT
            ON id = dt.dataid 
WHERE   defid = 3070056 
        AND attrid = 4

SELECT  ID
FROM    llattrdata ad
        INNER JOIN DTREE DT 
            ON ad.id = dt.dataid 
WHERE   defid = 3070056 
        AND attrid = 4
        AND VERNUM = MAX_VERNUM
        AND DEFVERN = MAX_DEFVERN;

Upvotes: 1

Related Questions