Create Table Failed. 2646: No more spool space in QJK8SNK

Database
Enthusiast

Create Table Failed. 2646: No more spool space in QJK8SNK

Hi,

I am getting the above error while executing the codes:

 

create table DDWUTDC.qjk8snk_OTHER_EXC11TMP as
(select distinct (a.CLNT_NO) as CLNT_NO
,' ' as HOUSEHOLD
,' ' as TELEPHONE
,date '2018-04-20' As LAST_DAY
, 'Crisis - Humboldt(by Province)' as REASON
From (
(Select Distinct( clnt.CLNT_NO)
From DDWV01.RB_CLNT_DLY clnt
Left Join DDWV01.CLNT_AR_RELTN_DLY carn
on COALESCE(clnt.CLNT_NO,'') = COALESCE(carn.CLNT_NO,'') and
carn.SNAP_DT = (select max(SNAP_DT) from DDWV01.CLNT_AR_RELTN_DLY where SNAP_DT>= date-7)
Left join DDWV01.AR_ADDR_RELTN_DLY adrn
on COALESCE(adrn.AR_ID,'') = COALESCE(carn.AR_ID,'') and
adrn.SNAP_DT = (select max(SNAP_DT) from DDWV01.AR_ADDR_RELTN_DLY where SNAP_DT>= date-7)
Left join DDWV01.ADDR_DLY addr
on COALESCE(addr.ADDR_ID,'') = COALESCE(adrn.ADDR_ID,'') and
addr.SNAP_DT = (select max(SNAP_DT) from DDWV01.ADDR_DLY where SNAP_DT>= date-7) and
(addr. PSTL_ZIP_CD like 'S%')
Where clnt.SNAP_DT = (select max(SNAP_DT) from DDWV01.RB_CLNT_DLY where SNAP_DT>= date-7) and
addr.ST_CNTRY_NM is not null
)
union
(Select Distinct (rbclnt.CLNT_NO)
From dg6v01.clnt rbclnt
Left Join dg6v01.ADDR addr
on rbclnt.ADDR_ID = addr.ADDR_ID
Where rbclnt.ACTV_INA_STS = 'A' and
(addr.FSA_CD like 'S%')
)
union
(Select Distinct (vsac.CLNT_NO)
From d3cv12a.visa_acct_r as vsac
Left Join d3cv12a.ADDR addr
on vsac.ADDR_ID = addr.ADDR_ID and
addr.DT_RECORD_EXT = (select me_dt from d3cv12a.load_cntrl)
Where vsac.dt_record_ext = (select me_dt from d3cv12a.load_cntrl) and
(addr.PSTCD like 'S%')
)
) as a
) with data primary index (clnt_no);

 

I am getting the same error even executing the first query without the 'union' the tables:

 

create table DDWUTDC.qjk8snk_OTHER_EXC11TMP as
(select distinct (a.CLNT_NO) as CLNT_NO
,' ' as HOUSEHOLD
,' ' as TELEPHONE
,date '2018-04-20' As LAST_DAY
, 'Crisis - Humboldt(by Province)' as REASON
From (
(Select Distinct( clnt.CLNT_NO)
From DDWV01.RB_CLNT_DLY clnt
Left Join DDWV01.CLNT_AR_RELTN_DLY carn
on COALESCE(clnt.CLNT_NO,'') = COALESCE(carn.CLNT_NO,'') and
carn.SNAP_DT = (select max(SNAP_DT) from DDWV01.CLNT_AR_RELTN_DLY where SNAP_DT>= date-7)
Left join DDWV01.AR_ADDR_RELTN_DLY adrn
on COALESCE(adrn.AR_ID,'') = COALESCE(carn.AR_ID,'') and
adrn.SNAP_DT = (select max(SNAP_DT) from DDWV01.AR_ADDR_RELTN_DLY where SNAP_DT>= date-7)
Left join DDWV01.ADDR_DLY addr
on COALESCE(addr.ADDR_ID,'') = COALESCE(adrn.ADDR_ID,'') and
addr.SNAP_DT = (select max(SNAP_DT) from DDWV01.ADDR_DLY where SNAP_DT>= date-7) and
(addr. PSTL_ZIP_CD like 'S%')
Where clnt.SNAP_DT = (select max(SNAP_DT) from DDWV01.RB_CLNT_DLY where SNAP_DT>= date-7) and
addr.ST_CNTRY_NM is not null
)

) as a
) with data primary index (clnt_no);

 

 

Can someone tell me what went wrong?

 

Thanks

Stephen

3 REPLIES
Teradata Employee

Re: Create Table Failed. 2646: No more spool space in QJK8SNK

Why do you have the COALESCE's in the join predicates? Do you really want all rows with blank/null in one table to match all rows with blank/null in the other?

Enthusiast

Re: Create Table Failed. 2646: No more spool space in QJK8SNK

Yes.

 

Would this trigger the error?

 

Thanks

Stephen

Teradata Employee

Re: Create Table Failed. 2646: No more spool space in QJK8SNK

Yes!  The way this is written (with join columns Coalesce()'ing to the same value), you will get product joins, because all rows with the missing Addr_ID's and AR_ID's will match each other.  Every such row will be joined to every other such row.

Find something that is not null to join on. A join is supposed to connect on key attributes (columns) that identify an entity (row).