SQL question

From: Mike Trozzo <lock_and_load62_at_hotmail.com>
Date: Tue, 23 Apr 2002 13:10:48 GMT
Message-ID: <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 / Received on Tue Apr 23 2002 - 15:10:48 CEST