Re: SQL problem

Any SQL you put into a stored procedure or function should always be tested at the SQL*Plus command prompt to validate it.

In your case ... you would likely find that you need a GROUP BY clause.

Daniel Morgan

Mike Trozzo wrote:

> 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 Tue Apr 23 2002 - 10:35:25 CDT