Re: Explain THIS, please!

In <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
> 16 GROUP BY ss.part_key
> 17 ORDER BY SUM(ss.cost) desc )
> 18 GROUP BY '%',
> 19 p.prefix||'-'||p.base_code||'-'||p.suffix||p.control_code,
> 20 s.scrap_code
> 21 ORDER BY p.prefix||'-'||p.base_code||'-'||p.suffix||p.control_code,
> 22* SUM(s.cost) desc
 

>SQL> /
> ORDER BY SUM(ss.cost) desc )
> *
>ERROR at line 17:
>ORA-00907: missing right parenthesis
 

>SQL> spool off
 

>There certainly AREN'T any mismatched parentheses!
 

>Any ideas what is going on?

To answer your question: Take the order by clause out of the subquery.

To answer your problem, (the reason you had the order by in, was to try to

                         get the top 10 somethings) Try rewriting the in as
                         a correlated Exists subquery with a nested subquery
                         to check that it is in the top 10 items. 

Example.

Table animals
ID number,
Kind char(30),
Name char(30)

to select the id for the top 10 names within a kind, use the following query.

SELECT ID
FROM ANIMALS
WHERE 10 >= ( SELECT COUNT(*)

               FROM   ANIMALS CNT
               WHERE  CNT.KIND  = ANIMALS.KIND
               AND    CNT.NAME <= ANIMALS.NAME )

				Craig