Spool Space error...please help to optimize

Database
Enthusiast

Spool Space error...please help to optimize

I am trying to pull some information from Teradata SQL Assistant.  The following query keeps giving me a spool space error.  Is there anything you can think of that I can do differently to avoid this?

SELECT DISTINCT

A.USER_ID,

C.SUPER_NM_LVL2 AS SUP,

C.SUPER_NM_LVL1 AS AD,

DIR.SUPER_NM_LVL2 AS DIR,

A.ACTIVITY_DT,

B.MTN,

A.ACCT_NUM,

A.PPLAN_CD_CURR

FROM DLY_LINE_ACTIVITY_PPLAN_V AS A

LEFT OUTER JOIN

SUBS_SUM_FACT_V AS B

ON A.ACCT_NUM=B.ACCT_NUM

LEFT OUTER JOIN

HR_EMPLOYEE_V AS C

ON A.USER_ID=C.NT_USER_ID

LEFT OUTER JOIN

HR_EMPLOYEE_V AS DIR

ON C.SUPER_ID_LVL2_EMP_ID=DIR.EMP_ID

WHERE

A.ACTIVITY_DT >= '2012-06-28'

AND A.ACTIVITY_DT <= '2012-11-19'

AND C.EMP_AREA_CD='WE'

AND A.PPLAN_CD_CURR IN ('86489', '86491', '86492', '86494', '86495', '86496', '86497', '86498', '86499', '86500', '86501', '86502', '86487', '86489', '86504', '86505', '86506', '86507', '86508', '86509', '86510', '86511', '86512')

GROUP BY

A.USER_ID,

C.SUPER_NM_LVL2,

C.SUPER_NM_LVL1,

DIR.SUPER_NM_LVL2,

A.ACTIVITY_DT,

B.MTN,

A.ACCT_NUM,

A.PPLAN_CD_CURR

ORDER BY

A.USER_ID,

C.SUPER_NM_LVL2,

C.SUPER_NM_LVL1,

DIR.SUPER_NM_LVL2,

A.ACTIVITY_DT,

B.MTN,

A.ACCT_NUM,

A.PPLAN_CD_CURR

1 REPLY
Enthusiast

Re: Spool Space error...please help to optimize

Try the below query:

SELECT DISTINCT

A.USER_ID,

C.SUPER_NM_LVL2 AS SUP,

C.SUPER_NM_LVL1 AS AD,

DIR.SUPER_NM_LVL2 AS DIR,

A.ACTIVITY_DT,

B.MTN,

A.ACCT_NUM,

A.PPLAN_CD_CURR

FROM (SELECT DISTINCT USER_ID,ACCT_NUM,ACTIVITY_DT,PPLAN_CD_CURR FROM DLY_LINE_ACTIVITY_PPLAN_V) AS A

LEFT OUTER JOIN

(SELECT DISTINCT MTN,ACCT_NUM FROM SUBS_SUM_FACT_V) AS B

ON A.ACCT_NUM=B.ACCT_NUM

LEFT OUTER JOIN

(SELECT DISTINCT NT_USER_ID,SUPER_ID_LVL2_EMP_ID,SUPER_NM_LVL2,SUPER_NM_LVL1 FROM HR_EMPLOYEE_V) AS C

ON A.USER_ID=C.NT_USER_ID

LEFT OUTER JOIN

(SELECT DISTINCT EMP_ID,SUPER_NM_LVL2 FROM HR_EMPLOYEE_V) AS DIR

ON C.SUPER_ID_LVL2_EMP_ID=DIR.EMP_ID

WHERE

A.ACTIVITY_DT >= '2012-06-28'

AND A.ACTIVITY_DT <= '2012-11-19'

AND C.EMP_AREA_CD='WE'

AND A.PPLAN_CD_CURR IN ('86489', '86491', '86492', '86494', '86495', '86496', '86497', '86498', '86499', '86500', '86501', '86502', '86487', '86489', '86504', '86505', '86506', '86507', '86508', '86509', '86510', '86511', '86512')

GROUP BY

A.USER_ID,

C.SUPER_NM_LVL2,

C.SUPER_NM_LVL1,

DIR.SUPER_NM_LVL2,

A.ACTIVITY_DT,

B.MTN,

A.ACCT_NUM,

A.PPLAN_CD_CURR

ORDER BY

A.USER_ID,

C.SUPER_NM_LVL2,

C.SUPER_NM_LVL1,

DIR.SUPER_NM_LVL2,

A.ACTIVITY_DT,

B.MTN,

A.ACCT_NUM,

A.PPLAN_CD_CURR