Re: Oracle 9 Query Question.....Please help....

Mark D Powell wrote:
> OK, different hint:
>
> select a.empno, a.sal + nvl(comm,0) as total_sal
> from emp a
> where (a.sal + nvl(a.comm,0) ) = (select max(b.sal + nvl(b.comm,0)) as
> total_sal
> from emp b)
> /
>
> EMPNO TOTAL_SAL
> ---------- ----------
> 7839 5000
>
> HTH -- Mark D Powell --

More thoroughly confused than ever...

I'm assuming that you were referring to the subquery, hence.

SQL> select orderdetails.prodnum,max(sum(products.price*orderdetails.unit)

  2 from products,orderdetails

  3 where products.prodnum=orderdetails.prodnum

  4 and (sum(products.price*orderdetails.unit)) = (select max(sum(products.price*orderdetails.unit)))

  5 group by orderdetails.prodnum,unit;

and sum(products.price*orderdetails.unit) = (select max(sum(products.price*orderdetails.unit)))

     *

ERROR at line 4:

ORA-00934: group function is not allowed here

I think it is how I'm interpreting the table joins, in this particular practice,

The orderdetails table contains the order number, product number, units and shipdate.

The products table contains, the product number, name, type, description, weight and price.

Somehow, I'm mixing up the parameters in my statement.

In any case, it was a "just for fun" problem, so I give up; cannot tell you how much time I've already spent on this!!!.

Thanks again for the replies and help!!! Received on Mon Apr 17 2006 - 13:16:43 CDT