Home -> Usenet -> comp.databases.theory -> Re: View challenge

Re: View challenge

From: J M Davitt <jdavitt_at_aeneas.net>
Date: Sat, 12 Aug 2006 07:59:25 GMT
Message-ID: <xTfDg.48016$vl5.26304_at_tornado.ohiordc.rr.com> 






frebe73_at_gmail.com wrote:

>>I suspect you left out a few details from your question. For instance,
>>it appears a natural join on emp_schedule and workshift suffice for your
>>view.




> 

> 

> In the schedule table you have a column recur_interval that indicates


> the number of days for which the workshifts should recur. A natural

> join will only return the first occurrence. For example with the given

> rows in the tables,

> 

> schedule(1, 7)

> 

> workshift(1, 1, '2006-08-07 08:00', '2006-08-07 17:00')

> workshift(1, 2, '2006-08-08 08:00', '2006-08-07 17:00')

> 

> employee(1)

> 

> emp_schedule(1, 1, '2006-08-07', '2006-08-21')

> 

> the view should return

> 

> (1, '2006-08-07 08:00', '2006-08-07 17:00')

> (1, '2006-08-08 08:00', '2006-08-08 17:00')

> (1, '2006-08-14 08:00', '2006-08-14 17:00')

> (1, '2006-08-15 08:00', '2006-08-15 17:00')

> 

> /Fredrik Bertilsson

> 




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.



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.



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.



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.
Received on Sat Aug 12 2006 - 09:59:25 CEST












Original text of this message