Re: View challenge
From: J M Davitt <jdavitt_at_aeneas.net>
Date: Sat, 12 Aug 2006 15:00:21 GMT
Message-ID: <92mDg.48035$vl5.38862_at_tornado.ohiordc.rr.com>
>> 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.
Date: Sat, 12 Aug 2006 15:00:21 GMT
Message-ID: <92mDg.48035$vl5.38862_at_tornado.ohiordc.rr.com>
Bob Badour wrote:
> J M Davitt wrote: >
>> 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.
Exactly right. But can it be done in MySQL's SQL? Received on Sat Aug 12 2006 - 17:00:21 CEST