SQL problem

From: Mike Trozzo <lock_and_load62_at_hotmail.com>
Date: Tue, 23 Apr 2002 04:31:07 GMT
Message-ID: <fs5x8.14991$uV.1019@nwrddc01.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. 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 Mon Apr 22 2002 - 23:31:07 CDT