Re: SQL Column ranges into rows

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.