Re: Setting WHERE on a VIEW with aggregate function.

On Fri, Sep 16, 2005 at 04:56:25PM -0700, Roger Hand wrote:
> So it would appear to me that you won't able to meet your goal by simply using a view.
>
> However, there are other solutions you can use which may or may not be appropriate. I can think of three at the moment.
>
> #1: Function Solution:

I'll take a look at this. It's about time I started to learn about
functions a bit.

> This would be the best solution if you are in control of the
> application source code. In Java, for example, it's relatively
> simple to call this function and return the result as a result set.
> If you're working in Java I'd be glad to show you same sample code.

I'm using Perl and DBI (really Class::DBI but DBI is not far away).

> #2: Simplify the Select Criteria Solution:
> =============================
>
> A slightly less flexible approach, but one that may be workable, would be to add two boolean columns (with default value of '0') to the class table: "completed" and "current". Then once a semester you run a simple query that updates them. Something like:
>
> UPDATE class SET current = '1' where class_time = '2005-09-01';
> UPDATE class SET completed = '1' where class_time < '2005-09-01';

Classes start daily (and at different hours). I could cron once an
hour I suppose, but I'd rather not de-normalize the data.

Maybe I can just create three views (future, recent, old) and live
with that.

The temporary table is another possibility I'll look into.

Thank you very much for spending time on this. I really appreciate
it.

--
Bill Moseley
moseley(at)hank(dot)org