Re: SQL question

"Mike Trozzo" <lock_and_load62_at_hotmail.com> wrote in news:s3dx8.17764$t65.11122_at_nwrddc02.gnilink.net:

> Hello,
>
> I'm running into an SQL problem that I'm not quite sure how to get
> around, and I'm hopefule that someone can help.
>
> I'm writing a stored function (listed below) that reads in the SKU of
> an item in inventory. In the "clothes" table, sku is the PK, and
> therefore unique, with qty_onhand being one of the columns. In the
> "orders_detail" table, there can be many rows containing the same SKU.
> SKU is a foreign key referencing clothes.sku. What I'm trying to do is
> to take the sum of qty_ordered in orders_detail for the SKU passed in,
> and add it to qty_onhand, and return that sum as the total_qty. My gut
> tells me I may be off base in line 5, because when I take one or the
> other out, it works fine. When I run this, I get this error:
>
> SQL> select avail('0001') from dual;
> select avail('0001') from dual
> *
> ERROR at line 1:
> ORA-00937: not a single-group group function
> ORA-06512: at "MIKE.AVAIL", line 5
>
> Any guidance in the right direction would be greatly appreciated.
>
> Thanks,
> Mike Trozzo
>
>
> SQL> create or replace function availables (sku_in varchar2)
> 2 return number is
> 3 total_qty number;
> 4 begin
> 5 select (c.qty_onhand + sum(d.qty_ordered))
> 6 into total_qty
> 7 from clothes c, orders_detail d
> 8 where c.sku = d.sku
> 9 and d.sku = sku_in;
> 10 return total_qty;
> 11 end availables;
> 12 /

The problem is that you're combining a group function (sum(d.qty_ordered)) with a scalar item (c.qty_onhand). You can fix this by selecting (sum(c.qty_onhand) + sum(d.qty_ordered)) making all the operators in the expression group functions.

-- 
Ken Denny
http://www.kendenny.com/

Give me ambiguity or give me something else!