Re: View challenge
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 12 Aug 2006 15:27:18 GMT
Message-ID: <qrmDg.39804$pu3.533163_at_ursa-nb00s0.nbnet.nb.ca>
>> 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?
Date: Sat, 12 Aug 2006 15:27:18 GMT
Message-ID: <qrmDg.39804$pu3.533163_at_ursa-nb00s0.nbnet.nb.ca>
J M Davitt wrote:
> 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?
Can anything? Received on Sat Aug 12 2006 - 17:27:18 CEST