No spool space

Database

No spool space

Hi All,

Actually we have 2 webi reports which are same only except the filters.
1. one has date filter where cal day key is the dimension object which we have as filer which prompts for" to date" and "from date"
2. one has week filter where WK_KEY is the filter object which prompts for week number ex:week 37 (7 days)

when we running the SQL in teradata for 7 days using date key its fetching data however when we tried running the same report with week key which also contains 7 days throws No more spool space error.

the only SQL difference is only in the join condition in both the reports are as shown below

1. For Week filter Report : Week filter is coming from table BV_CAL_WEEK. not fetching results

INNER JOIN BV_CAL_WEEK_YOY CAL_WEEK_YOY ON (CAL_WEEK_YOY.RPT_WK_KEY=MAT_MOVEMENT.FISCALWEEK AND CAL_WEEK_YOY.DEPTH=0 AND MAT_MOVEMENT.SOURCE=('POS') AND MAT_MOVEMENT.SPECIALSTOCKIND IN (CASE WHEN MAT_MOVEMENT.SOURCE= 'SAP' THEN 'W' ELSE MAT_MOVEMENT.SPECIALSTOCKIND END))
INNER JOIN BV_CAL_WK RPT_WK ON (RPT_WK.WK_KEY=CAL_WEEK_YOY.RPT_WK_KEY AND RPT_WK.CAL_TYPE_CD='LS')
INNER JOIN BV_CAL_YR CAL_YR ON (CAL_YR.YR_KEY=RPT_WK.YR_KEY AND CAL_YR.CAL_TYPE_CD='LS' AND RPT_WK.CAL_TYPE_CD='LS')

We tried to run individuals tables and found all tables are fetching results .only when we try this join as shown below its taking so much time
when i just tried to run only joining two tables from above for only 1 customer number still its taking lot of time n end with no spool space error -- select * from MAT_MOVEMENT inner join BV_CAL_WK on
MAT_MOVEMENT.FISCALWEEK =RPT_WK.WK_KEY where MAT_MOVEMENT.CUSTOMER='1000563'

2. For Date filter report :Date filter coming from table BV_CAL_DAY--query fetching results

INNER JOIN SV_CAL_DAY_CURRENT_PRIOR CAL_DAY_CURRENT_PRIOR ON (CAL_DAY_CURRENT_PRIOR.JOIN_DAY_KEY=MAT_MOVEMENT.PSTNG_DATE AND CAL_DAY_CURRENT_PRIOR.DEPTH = 0 AND MAT_MOVEMENT.SOURCE=('POS') AND MAT_MOVEMENT.SPECIALSTOCKIND IN (CASE WHEN MAT_MOVEMENT.SOURCE= 'SAP' THEN 'W' ELSE MAT_MOVEMENT.SPECIALSTOCKIND END) AND CAL_DAY_CURRENT_PRIOR.CAL_TYPE_CD=('LS'))
INNER JOIN BV_CAL_DAY CAL_DAY ON (CAL_DAY.CAL_DAY_KEY = CAL_DAY_CURRENT_PRIOR.RPT_DAY_KEY AND CAL_DAY.CAL_TYPE_CD = CAL_DAY_CURRENT_PRIOR.CAL_TYPE_CD AND CAL_DAY.CAL_TYPE_CD=('LS') AND CAL_DAY_CURRENT_PRIOR.CAL_TYPE_CD=('LS'))

Please suggest some technique how as to rectify this issue ...how to approach ...what could be the reason for this week report which just run for long time n throws no spool space error ...

Thanks,
Deepa

4 REPLIES
Enthusiast

Re: No spool space

Is it possible that you are skewing on the week and blowing spool on a few amps?  I don't like using week_code just for that reason.  Maybe keep it at the day grain calculate the week within the BI tool?  No sure.... just taking some wild stabs.

Re: No spool space

Thanks for the reply howwever week key holds the same 7 days which we use in date report so what's the difference ... how is it causing the issue

Junior Contributor

Re: No spool space

Are you sure that YR_KEY and WK_KEY are unique within your calendar, i.e. one row per year/week?

Can you show both Explains?

Re: No spool space

yes both are unique only still we are struggline with the issue .