Re: query dilemma

From: Spencer <spencerp_at_swbell.net>
Date: Sat, 27 Jan 2001 18:12:05 -0600
Message-ID: <CKJc6.147$Pn.7737@nnrp1.sbc.net>

You might try something like this for better performance:

create a function to simply convert the "cow count" into the desired "farm_size" string:

FUNCTION farm_size
(an_cow_count IN NUMBER
)RETURN VARCHAR2
IS
BEGIN
  IF an_cow_count <= 50 THEN
    RETURN 'BIG';
  ELSIF an_cow_count <= 100 THEN
    RETURN 'MEDIUM';
  ELSIF an_cow_count > 100 THEN
    RETURN 'SMALL';
  ELSE
    RETURN NULL;
  END IF;
END farm_size;

and the SQL would look like this:

SELECT f.FARM_TOWN

, TRUNC(c.CHECK_DATE,'MONTH') As check_month
, FARM_SIZE(w.cow_count) As farm_size
, COUNT(*)

  FROM FARMS f

, CHECKS c
, (SELECT x.FARM_CODE

             , x.CHECK_DATE
             , COUNT(*) As cow_count
          FROM CHECKS x
         GROUP BY x.FARM_CODE, x.CHECK_DATE
       )w
 WHERE f.FARM_CODE  = c.FARM_CODE

   AND c.FARM_CODE = w.FARM_CODE
   AND c.CHECK_DATE = w.CHECK_DATE
 GROUP BY f.FARM_TOWN

        , TRUNC(CHECK_DATE,'MONTH')
        , FARM_SIZE(w.cow_count)

I haven't tested this, but i'm thinking this will give you the same result set you were getting with the previous query.

The values returned for various counts of cows seem a little strange, since 4 cows would be classified 'BIG' and 240 cows would be classified 'SMALL'... but that aside...

The function is not strictly necessary, you could achieve the same result with a combination of simple arithmetic and the SIGN and DECODE functions.

For example, if you what you want is:
  if cow_count < 51 then return 'BIG'

the "test" can converted as shown in the following steps:

       cow_count       <  51
      (cow_count - 51) < (51 - 51)
      (cow_count - 51) <  0
  SIGN(cow_count - 51) = -1

now that you have the test converted to an equality test, you can used it in a DECODE function, like this:

DECODE(SIGN(cow_count-51),-1.'BIG','whatever')

of course, you can replace the last expression with any expression, including another DECODE function... such that the following expression could replace call to the FARM_SIZE function (in both places in the query)

DECODE(SIGN(w.cow_count-51),-1.'BIG'
  ,DECODE(SIGN(w.cow_count-101),-1,'MEDIUM'     ,'SMALL')) personally, i think the function makes the SQL more readable.

HTH "Brian Dick" <bdick_at_home.com> wrote in message news:7vhc6.17221$R62.190489_at_news1.wwck1.ri.home.com...

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

Received on Sat Jan 27 2001 - 18:12:05 CST