Re: Oracle Subquery Question

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Tue, 23 Jan 2001 14:52:31 GMT
Message-ID: <94k5rb$rvv$1@nnrp1.deja.com>

In article <3a6ce5f7.202253889_at_news.supernews.com>,   sl_at_xxxsigatl.com (SL) wrote:
> I am having a problem with what should be a simple query. The
> following query is returning a ORA-00936, 'Missing Expression' error
> message.
>
> select a.part_no,
> (SELECT sum(qty_onhand) FROM inventory_part_location_tab b
> WHERE a.part_no = b.part_no)
> from inventory_part_tab a
>
> I am trying to return two fields, part_no and a sum of quantities.
> What am I missing?
>
> TIA
>

I am not an sql expert so I am not sure of the exact problem, but I do know how to write this query to get the sum of the quanity for a part. You are trying to perform a group function, sum, on ungrouped data so I would chang the query to be like:
UT1> l
  1 select a.part_no, b.qty_no_hand
  2 from inventory_part a,

  3        (select c.part_no, sum(c.qty_onhand) as qty_no_hand
  4          from inventory_part_location c
  5          group by c.part_no ) b

  6* where a.part_no = b.part_no
UT1> /

   PART_NO QTY_NO_HAND
---------- -----------

         1          62
         2         160

Where the raw data looks like:
UT1> select * from inventory_part;

   PART_NO

---

         1
         2

UT1> select * from inventory_part_location;

   PART_NO QTY_ONHAND
---------- ----------

         1          2
         1         60
         2         60
         2        100

You may want to consider using an outer join so that if there is no location entry for a part the part still shows up in the query result, but that will depend on what you are actually trying to report and why.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/

Received on Tue Jan 23 2001 - 08:52:31 CST