Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: query dilemma
Come on David!
You should know: It's not the size of your cow, but where you milk it.
Regards
HJR
David Fitzjarrell <oratune_at_aol.com> wrote in message
news:94s9e6$v36$1_at_nnrp1.deja.com...
> In our last gripping episode Cristian Veronesi <c.veronesi_at_crpa.it>
> wrote:
> > I have two tables (Oracle 7.3.4) :
> >
> > Farms (PK: farm_code)
> > Checks (PK: farm_code+check_date+cow_id)
> >
> > I have to perform a query like this:
> >
> > select farms.farm_town,
> > trunc(check_date,'MONTH') as check_month,
> > ??? as farm_size,
> > count(*)
> > from farms, checks
> > where farms.farm_code =3D checks.farm_code
> > group by farms.farm_town, trunc(check_date,'MONTH'), [farm_size] ;
> >
> > The problem is that the only way to determine the farm size at a
> > particular date is to count the number of cows checked in that date. I
> > then wrote a stored function getFarmSize...
> >
> > function getFarmSize (
> > farm_code_in in varchar2,
> > check_date_in in varchar2
> > ) return varchar2 is
> > cow_count integer ;
> > begin
> > select count(*)
> > into cow_count
> > from checks
> > where farm_code =3D farm_code_in
> > and check_date =3D check_date_in ;
> > if cow_count between 1 and 50 then
> > return 'BIG' ;
> > elsif cow_count between 51 and 100 then
> > return 'MEDIUM' ;
> > elsif cow_count > 100 then
> > return 'SMALL' ;
> > end ;
> > end ;
> >
> > =2E.. and included it in the query this way:
> >
> > select farms.farm_town,
> > trunc(check_date,'MONTH') as check_month,
> > getFarmSize(checks.farm_code,check_date) as farm_size,
> > count(*)
> > from farms, checks
> > where farms.farm_code =3D checks.farm_code ;
> > group by farms.farm_town, trunc(check_date,'MONTH'),
> > getFarmSize(checks.farm_code,check_date) ;
> >
> > It works, but obviously the performance is very bad. Do you have some
> > ideas about to do the same thing with a better performance?
> >
> > TIA, Cris
> >
> > -- =
> >
> > Cristian Veronesi ><((((=BA> http://www.crpa.it
> >
> > If Bill Gates had a dime for every time a Windows box crashed...
> > Oh, wait a minute... he _already_ does! ;)
> >
>
>
>
>
>
> >