Re: SQL Column ranges into rows

Ok that wasn't quite right...

Here's the corrected:
create recursive view qtemp/myview
(code, thedate) as
select code, begindate
from t1 root
union all
select child.code, parent.thedate + 1 day
from myview parent, t1 child
where parent.code = child.code
and parent.theDate < child.enddate

select * from t1
CODE BEGINDATE ENDDATE
CHARLES 2008-01-15 2008-01-18
Pete 2009-01-30 2009-02-05

select * from myview
order by code, theDate
CODE THEDATE
CHARLES 2008-01-15
CHARLES 2008-01-16
CHARLES 2008-01-17
CHARLES 2008-01-18
Pete 2009-01-30
Pete 2009-01-31
Pete 2009-02-01
Pete 2009-02-02
Pete 2009-02-03
Pete 2009-02-04
Pete 2009-02-05

HTH,
Charles

On Fri, Jan 30, 2009 at 8:10 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

Ok, here's the view:

create recursive view qtemp/myview
(code, thedate) as
select code, begindate
from t1 root
union all
select child.code, parent.thedate + 1 day
from myview parent, t1 child
where parent.theDate < child.enddate


HTH,

Charles

On Fri, Jan 30, 2009 at 8:06 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

Ok I got it:

with cte (code, thedate) as
(select code, begindate
from t1 root
union all
select child.code, parent.thedate + 1 day
from cte parent, t1 child
where parent.theDate < child.enddate
)
select * from cte


still trying to get it into a view....

Charles


On Fri, Jan 30, 2009 at 7:59 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

I don't believe Elvis' or Birgitta's solution will work, as the way I
understand it, you want to take the single row from table2 and have it
exploded into the 5 rows of table1.

Note it'd be much, much easier to go the other way:
create view table1 as (
select code min(date) as beginDate, max(date) as endDate from table1
)

However, if you have to go from 2 to 1, then I see a few options:
with v5r4, I think you could use a recursive CTE....but I don't have a
lot of experience with recursive CTE and couldn't get it to work with
a few minutes of trying. Elvis or Birgitta probably could if it can
be done.

Other option, requires a calender table with dates in it so you can do this:
select code, theDate
from t2, datesTable
where datesTable.theDate between t2.startDate and t2.endDate

Lastly, you could maybe make use of a (table?) user defined function
of some sort.

Charles




On Thu, Jan 29, 2009 at 6:12 PM, Pete Helgren <Pete@xxxxxxxxxx> wrote:

Wresting with some database conversion issues. Actually, I don't want
to the database to change, I want my application that accesses these
different databases to change as little as possible so building views to
present the data in a similar fashion is what I am after.

I have a table that could either be in this format:

code, date
with data that looks like this:

'test', 20080115
'test', 20080116
'test', 20080117
'test', 20080118

OR I could have a table that looked like this:

code, begin date, end date
'test', 20080115, 20080118

What I would like do is have a view that structures the second table
like the first. If the two dates are the same, you get one row with
code and date. If you have a range of dates that span 6 days (for
example), then there would be 6 rows generated by the view.

Is this possible?

Thanks

Pete

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.