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