1

Below is a query that I've simplified quite substantially in the past week. It used to have 7 other joins, but now they are consolidated into two temp tables, so it's obviously not complex joins causing the error. But it still throws an 'Out of Memory' error about 75% of the time in Netezza, even on a new Windows task of Aginity (our Netezza IDE in Windows) to get new memory allocated. What is the most effective way to re-write this query to avoid the CASE statement altogether? The actual query has more than a dozen WHEN clauses, but I removed many of them for simplicity to write this question.

INSERT INTO SRC_ENC
(
    ENC_DATE,
    ENC_TYPE,
    ENC_NUM
)
SELECT
    TE.ENC_DT AS ENC_DATE,
    CASE 
        WHEN 
            TE.ARRIVAL_DT IS NOT NULL
            AND TE.ADMIT_DT IS NOT NULL
                THEN 'X1'
        WHEN
            TE.ARRIVAL_DT IS NOT NULL
            AND TE.ADMIT_DT IS NULL
                THEN 'X2'
        WHEN
            (
                TE.ADMIT_DT = TE.DISCH_DT
                AND INT4(TE.TYPE_CD) IN (3,104,126,501)
            )
            OR
            INT4(TE.TYPE_CD) = 104
                THEN 'X4'
        WHEN
            DEP.UNIT_TYPE = 2
                THEN 'X5'
        ELSE 'X0'
    END AS ENC_TYPE,
    TE.ENC_ID AS ENC_NUM
FROM 
    TEMP_ENC TE
    LEFT JOIN TEMP_DEP DEP ON TE.DEPT_ID = DEP.DEPT_ID
GROUP BY    
    ENC_DATE,
    ENC_TYPE,
    ENC_NUM 
JustBeingHelpful
  • 18,332
  • 38
  • 160
  • 245

0 Answers0