Recursive SQL combining Dates

General

Recursive SQL combining Dates

Hello,  Can someone help me with a Recursive SQL problem.  I am new to teradata and I know Recursive SQL is a more advanced function.  I'm trying to concatenate all the holiday dates on one line and my query runs but it never stops :).  Any Ideas?

 

WITH RECURSIVE Holiday (HOLIDAY_IND, Dates, level) AS

(

SELECT D.HOLIDAY_IND, D.DATE_DATE, 0

FROM DIM_DATE D

WHERE

D.HOLIDAY_IND IN ( 'Y' )

AND

D.DATE_DATE > add_months(trunc(Current_Date),-1)

UNION ALL

select D.HOLIDAY_IND , D.DATE_DATE , h.level+1

from Holiday H, DIM_DATE D

WHERE H.HOLIDAY_IND=D.HOLIDAY_IND

 

 

)

 

SELECT *

FROM Holiday

Tags (1)
2 REPLIES
Junior Supporter

Re: Recursive SQL combining Dates

Steve:

You need to limit the infinite loop with some aditional condition.

For example:

...

  from Holiday H, DIM_DATE D

WHERE H.HOLIDAY_IND=D.HOLIDAY_IND

   AND D.DAT_DATE <= '2012-09-31'

HTH

Cheers.

Carlos.

Enthusiast

Re: Recursive SQL combining Dates

Hi,

There is no doubt that you need to limit your SEED statement to setup a base table but it is equally important to limit the results when you are selecting the final result set.

If you introduce another column in the table which is more like a row number or some other unique number identifying for each row then this query should return you the desired results.

This query could be optimized further, but should give you idea as to how to get the desired results.

WITH RECURSIVE HOLIDAY (HOLIDAY_IND, DATE_DATE, Seq, lev) AS
(
SELECT tblA.HOLIDAY_IND, cast(tblA.DATE_DATE as varchar(10)), tblA.Seq, 0 as lev
FROM test_table tblA
where tblA.SEQ = ( SEL min(SEQ) as SEQ from test_table where HOLIDAY_IND = 'Y')

UNION ALL

select tblD.HOLIDAY_IND, cast(tblD.DATE_DATE as varchar(10)) || '-' || cast(tblC.DATE_DATE as varchar(10)), tblD.Seq, tblC.lev+1
from HOLIDAY tblC, test_table tblD
where tblD.HOLIDAY_IND = tblC.HOLIDAY_IND
And tblD.Seq > tblC.Seq

)

SELECT r.HOLIDAY_IND, r.DATE_DATE
FROM HOLIDAY r
where r.lev = (sel max(lev) as lev from HOLIDAY )

Cheers!