Re: Explain THIS, please!

From: <geemic_at_hhcs.gov.au>
Date: 23 Dec 92 15:03:26 +1100
Message-ID: <1992Dec23.150326.560_at_hhcs.gov.au>


In article <1992Dec22.163411.557_at_hhcs.gov.au>, champs_at_hhcs.gov.au writes:
> In article <1gtd91INN7ln_at_ef2007.efhd.ford.com>, wwm_at_ef5003.efhd.ford.com (Bill Meahan) writes:

>> I'm tearing my hair out at the following:
>> 
>> SQL> l
>>   1  select '%' Shift,
>>   2   p.prefix||'-'||p.base_code||'-'||p.suffix||p.control_code PartNumber,
>>   3   s.scrap_code Reason,
>>   4   SUM(s.cost) TotalDollars
>>   5   FROM cimcmms.scrap_count_history s, acg.part p
>>   6   WHERE s.part_key=p.key
>>   7    AND s.work_center_code = '75021'
>>   8    AND s.production_date BETWEEN '1-Dec-92' AND '13-Dec-92'
>>   9    AND s.shift LIKE '%'
>>  10    AND s.part_key IN ( SELECT ss.part_key
>>  11       FROM cimcmms.scrap_count_history ss
>>  12       WHERE ss.work_center_code = '75021'
>>  13        AND ss.production_date BETWEEN '1-Dec-92' AND '13-Dec-92'
>>  14        AND ss.shift LIKE '%'
>>  15        AND ROWID <11

> *****
> Did you mean ROWID or ROWNUM?
>
>> 16 GROUP BY ss.part_key >> 17 ORDER BY SUM(ss.cost) desc )
> **************************
> **************************
> WARNING ! WARNING ! ORDER BYs DON'T WORK IN SUB-QUERIES
> At least they don't in our version (VAX 3.0.9.7.1). As soon as we put an order
> by in a sub-query we got your error (group by worked fine though). The SQL
> reference says you can't have order by in a sub-query on insert,update, and
> delete but does not say they're illegal in a select.
>
> It looks to me like you're trying to retreive the 10 parts with the highest
> sum(cost). As far as I can tell it's impossible to retrieve the top 10 groups
> of anything in a single query. You can retrieve the top ten of something, but
> not the top 10 GROUPS of something. Your only chance is to use PL/SQL cursors
> (though you'll have to put the results of the query in a temporary table so
> that you can see them after the PL/SQL has finished, as the results of PL/SQL
> statements don't echo to the screen).
>
> Hope this helps.
> ------------------------------------------------------------------------------
> /\ Steven Champness
> / \ Dept Health Housing and Community Services
> / \ Brisbane Queensland
> / \ via
> / / \ Canberra Australia
> \ / / /
> \/ / / / MACINTOSH SOFTWARE AXIOM : If you need a manual to operate it
> \/ / / it wasn't designed properly in the
> \/ / first place !
> \/

Testing Steve... Received on Wed Dec 23 1992 - 05:03:26 CET

Original text of this message