CTE non stop recursiveness

[SQLScholar]

Hey all,

I am still dont 100% understand CTEs. We have a little issue with a CTE that doesnt stop running. Basically i need to create a list of every date, cross joined to a table with about 10 rows.

Without the cross join element it works perfectly. With it - it starts off working ok. Then it gets todays date - and then repeats it for infinity.

heres my code:

with ReportDates as
  (
  select cast('2011-01-07' as date) calendar_date, ORGANISATION_CODE
  from
  dbo.ORGANISATION_UNITS
  where LEN(ORGANISATION_UNITS.ORGANISATION_CODE) = 1
  union all
  select DATEADD(DAY,1,calendar_date), ORGANISATION_UNITS.ORGANISATION_CODE
  from ReportDates
  cross join 
  dbo.ORGANISATION_UNITS
  where DATEADD(DAY,1,calendar_date) < GETDATE()
  and LEN(ORGANISATION_UNITS.ORGANISATION_CODE) = 1
  )

select * from reportdates

OPTION (MAXRECURSION 0)

Any help gratefully recieved.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[SQLScholar]

DOH. I should do it in two - not one

with ReportDates as
  (
  select cast('2011-01-07' as date) calendar_date
  union all
  select DATEADD(DAY,1,calendar_date)
  from ReportDates
  where DATEADD(DAY,1,calendar_date) < GETDATE()
  ),
ReportDatesWithOrg as 
	( select calendar_date, ORGANISATION_CODE from ReportDates
	cross join
	ORGANISATION_UNITS
	where len(ORGANISATION_CODE) = 1 )


select * from ReportDatesWithOrg


OPTION (MAXRECURSION 0)

Sorry for troubling you.....

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------