Re: query dilemma

Try something like:

select farms.farm_town,
trunc(check_date,'MONTH') as check_month, farm_size,
count(*)
from farms, checks,
(select c_farm_code, c_check_date, count(*) as farm_size from checks
group by farm_code, check_date)
where farms.farm_code = checks.farm_code and checks.farm_code = c_farm_code
and checks.check_date = c_check_date
group by farms.farm_town, trunc(check_date,'MONTH'), farm_size;

"Cristian Veronesi" <c.veronesi_at_crpa.it> wrote in message news:3A71857A.8742E03_at_crpa.it...
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 = 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 = farm_code_in
  and check_date = 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 ;

... 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 = 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 ><((((º> http://www.crpa.it

If Bill Gates had a dime for every time a Windows box crashed...
Oh, wait a minute... he _already_ does! ;)