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>
> 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 !
> \/
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> WARNING ! WARNING ! ORDER BYs DON'T WORK IN SUB-QUERIES
> *****
> Did you mean ROWID or ROWNUM?
>
>> 16 GROUP BY ss.part_key >> 17 ORDER BY SUM(ss.cost) desc )
> **************************
> **************************
> 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