spool space error in where statements

I was given this query to run but it returned spool error. I was told to take the bolded where statement out and use it to create categoryG exclusions table and then add another new step to join that table with the rest of the table. I'm new to Teradata and got very lost. Hoping someone can help. Thank you!

CREATE VOLATILE TABLE A
    ,NO FALLBACK
    ,NO BEFORE JOURNAL
    ,NO AFTER JOURNAL AS 
(
    SELECT 'TX' AS STATE
        ,ADD_MONTHS(li.column7 - EXTRACT(DAY FROM li.Column21) + 1, 1) - 1 AS column12
        ,ADD_MONTHS(li.Column22 - EXTRACT(DAY FROM li.Column22) + 1, 1) - 1 AS column13
        ,EXTRACT(YEAR FROM column13) * 12 + EXTRACT(MONTH FROM column13) - EXTRACT(YEAR FROM column12) * 12 - EXTRACT(MONTH FROM column12) AS offset
        ,column8
        ,CASE 
            WHEN clm.Column20 = '01strong text'
                AND li.Column23 = '1'
                THEN 'INP'
            WHEN clm.Column20 = '01'
                AND li.Column23 IN (
                    '2'
                    ,'3'
                    )
                THEN 'OUT'
            WHEN clm.Column20 = '02'
                AND CHARACTER_LENGTH(TRIM(Column24)) <> 5
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 1 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 2 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 3 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 4 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 5 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND Column24 BETWEEN '00000'
                    AND '99999'
                THEN 'PRO'
            WHEN clm.Column20 = '02'
                THEN 'OME'
            WHEN clm.Column20 = '03'
                THEN 'PDP'
            WHEN clm.Column20 = '04'
                THEN 'DEN'
            ELSE ''
            END AS column9
        ,CASE 
            WHEN fcg.column15 IN (
                    '000166915'
                    ,'000166916'
                    ,'000166913'
                    ,'000168717'
                    ,'000168718'
                    )
                THEN 'Y'
            WHEN fcg.column15 IN (
                    '000168719'
                    ,'000166920'
                    ,'000166917'
                    ,'000166919'
                    ,'000166914'
                    ,'000168716'
                    )
                THEN 'N'
            WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '1'
                THEN 'Y'
            WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '2'
                THEN 'N'
            ELSE ''
            END AS column10
        ,CASE 
            WHEN fcg.Column25 IN (
                    '0004'
                    ,'0005'
                    ,'0006'
                    )
                THEN 'Y'
            ELSE 'N'
            END column11
        ,SUM(li.Column26) AS Column26
        ,SUM(li.Column27) AS Column27
        ,SUM(li.Column28) AS paid_amt
        ,SUM(CASE 
                WHEN clm.Column20 = '01'
                    THEN li.Column26 - li.non_covd_amt - li.Column30
                ELSE li.Column29
                END) AS amount 
    FROM DatabaseLi li 
        INNER JOIN DatabaseFCG fcg 
            ON fcg.column30 = li.column30
            AND li.disp_cd = 'A' 
        INNER JOIN Database11 clm 
            ON clm.column30 = li.column30 
        INNER JOIN Database11_mbr MBR 
            ON li.column30 = MBR.column30 
        LEFT JOIN / CategoryG exclusion / 
            (
                SELECT column30
                FROM Database11_src
                WHERE Column50IN('MA', 'H8')
                GROUP BY 1
            ) src 
            ON li.column30 = src.column30 
        INNER JOIN 
            (
                SELECT column15
                    ,CASE 
                        WHEN product_id LIKE '%apple%'
                            THEN 'apple'
                        WHEN product_id LIKE '%orange%'
                            THEN 'orange'
                        WHEN product_id LIKE '%banana%'
                            THEN 'apple'
                        ELSE ''
                        END AS column8
                FROM DatabaseDD
                WHERE lob IN ('Categrory3')
                    AND product_id IS NOT NULL
                    AND product_id NOT LIKE '%LV%'
                GROUP BY 1,2
            ) AS a 
            ON a.column15 = fcg.column15 
    /*This is the WHERE statement to remove*/
    WHERE /* Excluding ALL CategoryG after 2014 - 01 - 01 */

        li.column7 >= '2014-01-01'
        AND src.column30 IS NULL        
        AND clm.Column20 <> '04'
        AND NOT MBR.column55 IN (
                SELECT DISTINCT a.column55
                FROM DatabaseA a
                INNER JOIN DatabaseB b ON a.column1 = b.column1
                    AND a.column2 = b.column2
                    AND a.column3 = b.column3
                    AND a.column4 = b.column4
                    AND a.column5 = '11'
                    AND a.column14 IN (
                        SELECT DISTINCT column15
                        FROM DatabaseDD
                        WHERE lob LIKE '%Categrory3%'
                        )
                    AND a.column6 <> 'IND'
                )           
        AND NOT fcg.column15 IN (
                SELECT DISTINCT a.column14
                FROM DatabaseA a
                INNER JOIN DatabaseB b ON a.column1 = b.column1
                    AND a.column2 = b.column2
                    AND a.column3 = b.column3
                    AND a.column4 = b.column4
                    AND a.column5 = '11'
                    AND a.column14 IN (
                        SELECT DISTINCT column15
                        FROM DatabaseDD
                        WHERE lob LIKE '%Categrory3%'
                        )
                    AND a.column6 <> 'IND'
                )
        AND li.column7 < '2016-01-01'
    GROUP BY 1,2,3,4,5,6,7,8
    )
    WITH DATA UNIQUE PRIMARY INDEX (
            STATE
            ,column8
            ,column9
            ,column10
            ,column11
            ,column12
            ,column13
            ) ON COMMIT PRESERVE ROWS;

1 answer

  • answered 2018-03-14 14:21 JNevill

    Spool space is where intermediate result sets are stored. These often come from subqueries (but also tables that are being pre-filtered before a join and a million other things). So to get around your spool space issue, a good first step is removing your subqueries to volatile tables of their own. Then join those volatile tables in.

    This might look something like:

    CREATE MULTISET VOLATILE TABLE volatile_1 AS
    (
        SELECT DISTINCT a.column55
        FROM DatabaseA a
        INNER JOIN DatabaseB b ON a.column1 = b.column1
            AND a.column2 = b.column2
            AND a.column3 = b.column3
            AND a.column4 = b.column4
            AND a.column5 = '11'
            AND a.column14 IN (
                SELECT DISTINCT column15
                FROM DatabaseDD
                WHERE lob LIKE '%Categrory3%'
                )
            AND a.column6 <> 'IND'
    ) WITH DATA ON COMMIT PRESERVE ROWS;
    
    CREATE MULTISET VOLATILE TABLE volatile_2 AS
    (
        SELECT DISTINCT a.column14
        FROM DatabaseA a
        INNER JOIN DatabaseB b ON a.column1 = b.column1
            AND a.column2 = b.column2
            AND a.column3 = b.column3
            AND a.column4 = b.column4
            AND a.column5 = '11'
            AND a.column14 IN (
                SELECT DISTINCT column15
                FROM DatabaseDD
                WHERE lob LIKE '%Categrory3%'
                )
            AND a.column6 <> 'IND'
    ) WITH DATA ON COMMIT PRESERVE ROWS;
    
    CREATE MULTISET VOLATILE TABLE volatile_src AS
    (
        SELECT column30
        FROM Database11_src
        WHERE Column50IN('MA', 'H8')
        GROUP BY 1
    ) WITH DATA ON COMMIT PRESERVE ROWS;
    
    CREATE MULTISET VOLATILE TABLE volatile_src AS
    (
        SELECT column15
            ,CASE 
                WHEN product_id LIKE '%apple%'
                    THEN 'apple'
                WHEN product_id LIKE '%orange%'
                    THEN 'orange'
                WHEN product_id LIKE '%banana%'
                    THEN 'apple'
                ELSE ''
                END AS column8
        FROM DatabaseDD
        WHERE lob IN ('Categrory3')
            AND product_id IS NOT NULL
            AND product_id NOT LIKE '%LV%'
        GROUP BY 1,2
    ) WITH DATA ON COMMIT PRESERVE ROWS;
    
    CREATE VOLATILE TABLE A
        ,NO FALLBACK
        ,NO BEFORE JOURNAL
        ,NO AFTER JOURNAL AS 
    (
        SELECT 'TX' AS STATE
            ,ADD_MONTHS(li.column7 - EXTRACT(DAY FROM li.Column21) + 1, 1) - 1 AS column12
            ,ADD_MONTHS(li.Column22 - EXTRACT(DAY FROM li.Column22) + 1, 1) - 1 AS column13
            ,EXTRACT(YEAR FROM column13) * 12 + EXTRACT(MONTH FROM column13) - EXTRACT(YEAR FROM column12) * 12 - EXTRACT(MONTH FROM column12) AS offset
            ,column8
            ,CASE 
                WHEN clm.Column20 = '01strong text'
                    AND li.Column23 = '1'
                    THEN 'INP'
                WHEN clm.Column20 = '01'
                    AND li.Column23 IN (
                        '2'
                        ,'3'
                        )
                    THEN 'OUT'
                WHEN clm.Column20 = '02'
                    AND CHARACTER_LENGTH(TRIM(Column24)) <> 5
                    THEN 'OME'
                WHEN clm.Column20 = '02'
                    AND SUBSTRING(TRIM(Column24) FROM 1 FOR 1) BETWEEN 'A'
                        AND 'Z'
                    THEN 'OME'
                WHEN clm.Column20 = '02'
                    AND SUBSTRING(TRIM(Column24) FROM 2 FOR 1) BETWEEN 'A'
                        AND 'Z'
                    THEN 'OME'
                WHEN clm.Column20 = '02'
                    AND SUBSTRING(TRIM(Column24) FROM 3 FOR 1) BETWEEN 'A'
                        AND 'Z'
                    THEN 'OME'
                WHEN clm.Column20 = '02'
                    AND SUBSTRING(TRIM(Column24) FROM 4 FOR 1) BETWEEN 'A'
                        AND 'Z'
                    THEN 'OME'
                WHEN clm.Column20 = '02'
                    AND SUBSTRING(TRIM(Column24) FROM 5 FOR 1) BETWEEN 'A'
                        AND 'Z'
                    THEN 'OME'
                WHEN clm.Column20 = '02'
                    AND Column24 BETWEEN '00000'
                        AND '99999'
                    THEN 'PRO'
                WHEN clm.Column20 = '02'
                    THEN 'OME'
                WHEN clm.Column20 = '03'
                    THEN 'PDP'
                WHEN clm.Column20 = '04'
                    THEN 'DEN'
                ELSE ''
                END AS column9
            ,CASE 
                WHEN fcg.column15 IN (
                        '000166915'
                        ,'000166916'
                        ,'000166913'
                        ,'000168717'
                        ,'000168718'
                        )
                    THEN 'Y'
                WHEN fcg.column15 IN (
                        '000168719'
                        ,'000166920'
                        ,'000166917'
                        ,'000166919'
                        ,'000166914'
                        ,'000168716'
                        )
                    THEN 'N'
                WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '1'
                    THEN 'Y'
                WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '2'
                    THEN 'N'
                ELSE ''
                END AS column10
            ,CASE 
                WHEN fcg.Column25 IN (
                        '0004'
                        ,'0005'
                        ,'0006'
                        )
                    THEN 'Y'
                ELSE 'N'
                END column11
            ,SUM(li.Column26) AS Column26
            ,SUM(li.Column27) AS Column27
            ,SUM(li.Column28) AS paid_amt
            ,SUM(CASE 
                    WHEN clm.Column20 = '01'
                        THEN li.Column26 - li.non_covd_amt - li.Column30
                    ELSE li.Column29
                    END) AS amount 
        FROM DatabaseLi li 
            INNER JOIN DatabaseFCG fcg 
                ON fcg.column30 = li.column30
                AND li.disp_cd = 'A' 
            INNER JOIN Database11 clm 
                ON clm.column30 = li.column30 
            INNER JOIN Database11_mbr MBR 
                ON li.column30 = MBR.column30 
            LEFT JOIN volatile_src src 
                ON li.column30 = src.column30 
            INNER JOIN volatile_a AS a 
                ON a.column15 = fcg.column15 
        WHERE / Excluding ALL CategoryG after 2014 - 01 - 01 /      
            li.column7 >= DATE '2014-01-01'
            AND src.column30 IS NULL        
            AND clm.Column20 <> '04'
            AND NOT MBR.column55 IN (SELECT * FROM volatile_1)
            AND NOT fcg.column15 IN (SELECT * FROM volatile_2)
            AND li.column7 < '2016-01-01'
        GROUP BY 1,2,3,4,5,6,7,8
        )
        WITH DATA UNIQUE 
        PRIMARY INDEX 
            (
                STATE
                ,column8
                ,column9
                ,column10
                ,column11
                ,column12
                ,column13
            ) 
        ON COMMIT PRESERVE ROWS;
    

    If you are still running into spool issues, then break the query down. Remove all of the joins and any of the references to the joined tables in teh SELECT and WHERE clause, and run it. Does it spool out? If not, add the next one and it's related SELECT and WHERE clause items back in. Run it. Does it spool out? If not, add in the next table. Continue doing it until you spool out. You can zone in on how much data you can push through this one query.

    You may have to break this out into a few more statements using UPDATE statements to write more data into the records from the joins.

    You may have to beg your DBA for more spool. If this is a one time thing, perhaps someone with more SPOOL available can run it for you?