Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL problem

Re: SQL problem

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 23 Apr 2002 17:33:28 +0200
Message-ID: <vhvacusd9ttshrne26pngeddbokg65qa9d@4ax.com>


On Tue, 23 Apr 2002 04:31:07 GMT, "Mike Trozzo" <lock_and_load62_at_hotmail.com> 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 /
>

Obviously there can also be 0 orders
you need and other join between clothes and order_detail, so you would better have your where clause

c.sku = sku_in
and d.sku(+) = c.sku

also you need to have nvl(sum(d.qty_ordered),0) in your select list.

Homework area.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Apr 23 2002 - 10:33:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US