All Forums General
scascio 1 post Joined 06/12
27 Jun 2012
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:
CarlosAL 512 posts Joined 04/08
27 Jun 2012

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.

Qaisar Kiani 337 posts Joined 11/05
15 Jul 2012

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!

You must sign in to leave a comment.