Re: query dilemma

From: Van Messner <vmessner_at_bestweb.net>
Date: Sun, 28 Jan 2001 01:14:28 GMT
Message-ID: <UnKc6.1894$CQ4.201144@monger.newsread.com>

If cow 1 weighs 10874562870 pounds and cows 2 through 101 weigh only 3 pounds....

"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! ;)
> >

>

> You could try an in-line view with decode, but that would take much
> more work to implement as you would need to code for every possibility,
> up to 255, in the decode statement, and I don't think it would improve
> performance:

>

> select farms.farm_town,
> trunc(check_date,'MONTH') as check_month,
> farm_size,
> count(*)
> from farms, checks,
> ( select decode(count(*), 1, 'SMALL',
> 2, 'SMALL',
> 3, 'SMALL',
> 4, 'SMALL',
> ...) farm_size
> from checks
> where farm_code = checks.farm_code
> and check_date = check_month)
> where farms.farm_code = checks.farm_code
> group by farms.farm_town, trunc(check_date,'MONTH'),
> farm_size ;

>

> I can't, off the top of my hairy cranium, think of another way. Of
> course, I've been wrong before ...

>

> And I wonder about your size assignments -- somehow I can't quite
> understand that 1 cow makes a BIG farm and over 100 cows make a SMALL
> farm...

>

> --
> David Fitzjarrell
> Oracle Certified DBA

>
>

> Sent via Deja.com
> http://www.deja.com/
Received on Sat Jan 27 2001 - 19:14:28 CST