General
N/A

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

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_DATEFROM HOLIDAY rwhere r.lev = (sel max(lev) as lev from HOLIDAY ) `

Cheers!