No more spool space in a with recursive query.

Teradata Applications
K_Y
Enthusiast

No more spool space in a with recursive query.

Hi

 

I have table sales as below, my requirement is to replicate the product_id as per their quantity , I tried to achive it with recursive but i'm getting no more spool space issue, please help resolving it.

 

Table schema

CREATE MULTISET VOLATILE TABLE SALES
(
SALE_ID INTEGER,
PRODUCT_ID INTEGER,
YEAR1 INTEGER,
Quantity INTEGER,
PRICE INTEGER
)
ON COMMIT PRESERVE ROWS;

 

INSERT INTO SALES VALUES ( 1, 100, 2010, 2, 5000);
INSERT INTO SALES VALUES ( 2, 100, 2011, 1, 5000);
INSERT INTO SALES VALUES ( 3, 100, 2012, 2, 5000);
INSERT INTO SALES VALUES ( 4, 200, 2010, 1, 9000);
INSERT INTO SALES VALUES ( 5, 200, 2011, 1, 9000);
INSERT INTO SALES VALUES ( 6, 200, 2012, 2, 9000);
INSERT INTO SALES VALUES ( 7, 300, 2010, 2, 7000);
INSERT INTO SALES VALUES ( 8, 300, 2011, 1, 7000);
INSERT INTO SALES VALUES ( 9, 300, 2012, 2, 7000);

 

Query :


with recursive cte as
( select product_id, year1, quantity from sales
union all
select cte.product_id, cte.year1, cte.quantity-1 from cte inner join sales p
on cte.product_id=p.product_id
)
select * from cte;


Accepted Solutions
Ambassador

Re: No more spool space in a with recursive query.

There's no need to join:

WITH RECURSIVE cte AS
 (
   SELECT product_id, year1, quantity FROM sales
   UNION ALL
   SELECT cte.product_id, cte.year1, cte.quantity-1 
   FROM cte
   WHERE cte.quantity > 1
)
SELECT * FROM cte
ORDER BY 1,2,3;

And you can get the same result without recursion using EXPAND ON:

SELECT sales.*, End(pd) - Current_Date AS x
FROM sales
EXPAND ON PERIOD(Current_Date, Current_Date + quantity) AS pd

 

1 ACCEPTED SOLUTION
8 REPLIES 8
Teradata Employee

Re: No more spool space in a with recursive query.

Hi K_Y,

 

Recursive queries works with a anchor part to start the recursivity then loops on your joining condition.

Here, you have no starting point (thus every row starts a loop), and an infinite joining condition.

 

I'm not sure about the result you need, so I'll just add the quantity by product for the example (which can be done otherwise) :

with recursive cte_recurs (sale_id, product_id, year1, quantity, rn) as
(
select sale_id, product_id, year1, quantity, rn
  from cte_sales_rn
 where rn = 1
 union all
select cte.sale_id, cte.product_id, cte.year1, rec.quantity + cte.quantity, cte.rn
  from cte_recurs   as rec
  join cte_sales_rn as cte  on cte.product_id = rec.product_id
                           and cte.rn         = rec.rn + 1
)
  ,  cte_sales_rn (sale_id, product_id, year1, quantity, rn) as
(
select sale_id, product_id, year1, quantity
     , row_number() over(partition by product_id order by year1 asc)
  from SALES
)
  select sale_id, product_id, year1, quantity
    from cte_recurs
order by sale_id asc;

 

Ambassador

Re: No more spool space in a with recursive query.

There's no need to join:

WITH RECURSIVE cte AS
 (
   SELECT product_id, year1, quantity FROM sales
   UNION ALL
   SELECT cte.product_id, cte.year1, cte.quantity-1 
   FROM cte
   WHERE cte.quantity > 1
)
SELECT * FROM cte
ORDER BY 1,2,3;

And you can get the same result without recursion using EXPAND ON:

SELECT sales.*, End(pd) - Current_Date AS x
FROM sales
EXPAND ON PERIOD(Current_Date, Current_Date + quantity) AS pd

 

K_Y
Enthusiast

Re: No more spool space in a with recursive query.

Hi Dnoeth

 

thank you so much for the explanation !! I went thrugh few TD documents to understand working of expand on , but not getting it properly. Could you please explain a lillte on expand on function, begin, end, anchor and how it works.

 

Thanks

Kavita

K_Y
Enthusiast

Re: No more spool space in a with recursive query.

Walder

 

Thanks a lot for your explanation.

Ambassador

Re: No more spool space in a with recursive query.

EXPAND ON is used for creating Time Series and I'm abusing it :-)

 

EXPAND ON PERIOD(Current_Date, Current_Date + quantity)

returns one row per day in the Period and this Period is defined using your Quantity, e.g. Quantity 4 for SALE_ID 9 will return

 

 

SALE_ID PRODUCT_ID YEAR1 Quantity PRICE                       pd End(pd) - Current_Date
      9        300  2012        4  7000 (2019-07-09, 2019-07-10)                      1
      9        300  2012        4  7000 (2019-07-10, 2019-07-11)                      2
      9        300  2012        4  7000 (2019-07-11, 2019-07-12)                      3
      9        300  2012        4  7000 (2019-07-12, 2019-07-13)                      4

 

Begin/End extracts the start/end date from the Period, thus End(pd) - Current_Date calculates the number of days between Begin and End.

 

ANCHOR can be used to return specific rows, e.g. ANCHOR MONDAY returns only Mondays within the range of the Period

 

K_Y
Enthusiast

Re: No more spool space in a with recursive query.

Hi dnoeth

 

thank you so much for the explanation.

 

Thanks

Kavita

 

 

Teradata Employee

Re: No more spool space in a with recursive query.

Hi Kavita,

 

You missed the two things I told in a previous post.

1st one is to have an anchor to start the recursivity.

I'll go for a guess, in hierarchical management you can start with the top level, who have no manager.

So it should be this :

select empid, empname, managerid
  from employee
 where managerid is null

Then when you loop you join your recursive cte against the employee table.

But the join you're looking for is empid = managerid :

  from employee as e
  join emp      as e1 on e1.empid = e.managerid

If you join on itself (empid = empid), you're in an infinite loop thus the "no more spool" issued by the database.

Ambassador

Re: No more spool space in a with recursive query.

It spools out because you got an endless loop.

You must using e.managerid=e1.empid, otherwise you're not traversing the hierarchy.

 

In a Recursive query should always add two columns (at least while debugging), a number indicating the recursion level (limit the number until you imlemented the correct logic) and a string showing the path, i.e. a list of step.

 

 

WITH RECURSIVE cte AS
(
   SELECT empid,empname,managerid
     ,Cast(NULL AS VARCHAR(30)) AS mgrname -- data type should match empname 
     ,1 AS lvl -- as 1 is a BYTEINT, it's limited to 127 recursions, if you need more CAST to SMALLINT
     ,'.' || Cast(empname AS VARCHAR(500)) AS Path -- needed to sort your hierarchy, VarCahr size according to the maximum expected number of levels
   FROM employee AS e
-- you probably want to start with the root of the hierarchy -- if the root is indicated by "managerid IS NULL" or "managerid = 0" or "empid = managerid" it's simple
WHERE managerid IS NULL -- managerid = 0/empid = managerid
-- otherwise you need
WHERE NOT EXISTS ( SELECT * FROM employee AS e1 WHERE e1.empid = e.managerid -- doesn't exist as manager, i.e. start of hierarchy )
UNION ALL SELECT e.empid,e.empname,e.managerid ,cte.empname ,cte.lvl+1 ,Path || '.' || e.empname FROM employee AS e JOIN cte ON cte.empid=e.managerid -- current emp is previous manager WHERE cte.lvl < 2 -- start with a low number to check for correct logic
-- always specify a maximum recursion level, even if it's working now there might be future data resulting in an endless loop
-- to avoid visiting the same node again, i.e. a cyclic graph add
-- AND cte.Path NOT LIKE '%.' || e.empname || '.%'
) SELECT * FROM cte ORDER BY Path