Re: View challenge
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 12 Aug 2006 14:29:58 GMT
Message-ID: <GBlDg.39785$pu3.532017_at_ursa-nb00s0.nbnet.nb.ca>
>
>
> I'm not making myself clear; sorry.
>
> You're asking for more rows - not just different
> representations of existing data in existing rows.
>
> The best row-maker we've got is the cartesian
> product - but that won't "fill-in" any gaps: the
> only values you see "output" are representations
> of the "input."
>
> If you change the requirements so that that the
> interval [valid_from, valid_to] is constant - or,
> at least, limited to some reasonable value - and
> can presume that intervals for individuals don't
> cover each other, you can cobble together a bit
> of cheese that UNIONs a bunch of SELECT-crafted
> rows. Like the way Celko would do it.
Date: Sat, 12 Aug 2006 14:29:58 GMT
Message-ID: <GBlDg.39785$pu3.532017_at_ursa-nb00s0.nbnet.nb.ca>
> frebe73_at_gmail.com wrote:
>
>>> Well, that's not a view. See those tuples with values >>> like 2006-08-14 and 2006-08-15? Those values don't >>> appear in your tuples; you need something to generate >>> those. >> >> >> >> The values a view returns doesn't need to appear in the base tables. >> They may be calculated. The formula would be something like this: >> starttime + i*recur_interval >> >> >>> Views can present extant data in "different" ways - >>> sometimes the difference involves applying arithmetic >>> functions or aggregators to data and supplying values >>> that are not directly represented in the data. But >>> you seem to be asking for something different here. >> >> >> >> No, it is a simple arithmetic expression. >> >> >>> I've done lots of stuff like this - in Oracle, the >>> mechanism that looks like a table but behaves like a >>> function is called a table function and in postgres >>> they're just called functions. >> >> >> >> Obviously it would be no problem solving it, if the DBMS supports views >> that are backed up by a stored procedure. >> >> >>> My point is, your problem seems to require that you >>> make some data. The difficulty is that databases >>> store data and can present it in different forms. >>> If you require something other than what the database >>> provides, you'll have to write it, and it isn't a view. >> >> >> >> A RDBMS is capable of rather complex calculations. >> >> /Fredrick
>
>
> I'm not making myself clear; sorry.
>
> You're asking for more rows - not just different
> representations of existing data in existing rows.
>
> The best row-maker we've got is the cartesian
> product - but that won't "fill-in" any gaps: the
> only values you see "output" are representations
> of the "input."
>
> If you change the requirements so that that the
> interval [valid_from, valid_to] is constant - or,
> at least, limited to some reasonable value - and
> can presume that intervals for individuals don't
> cover each other, you can cobble together a bit
> of cheese that UNIONs a bunch of SELECT-crafted
> rows. Like the way Celko would do it.
Or he could use a closure to calculate the necessary series of integers or dates. Received on Sat Aug 12 2006 - 16:29:58 CEST