Re: Oracle Subquery Question

I am not sure if this is what you want. If not, I am sure someone can change it:

 SELECT a.part_no, a.description, SUM(b.qty_onhand)  FROM inventory_part_tab a,inventory_part_location_tab b  WHERE b.part_no =a.part_no)
 order by a.part_no;

--
Jining Han
Sallie Mae

In article <3a6d9029.5466282_at_news.supernews.com>,
  sl_at_xxxsigatl.com (SL) wrote:


> Any suggestions on how I can accomplish my goal?  I need to get the

> sum of the QTY_Onhand from multiple locations.

>

> On Tue, 23 Jan 2001 13:48:24 GMT, Jining Han <hanj_at_mailcity.com>

> wrote:

>

> >Only guessing here:  the 2nd one doesn't work because a as an alias

 is


> >out of scope.

> >

> >Jining Han

> >Sallie Mae

> >

> >In article <3a6d80ad.1502264_at_news.supernews.com>,

> >  sl_at_xxxsigatl.com wrote:

> >> Here's another twist.  The first of this two statements works; the

> >> second gets a ORA-0904 error.  Why?

> >>

> >> Works:

> >>

> >> SELECT a.part_no, a.description, NDF_Onhand

> >> FROM inventory_part_tab a,

> >> (SELECT SUM(qty_onhand) as NDF_Onhand FROM

 inventory_part_location_tab


> >> b

> >>  WHERE b.part_no = '11532')

> >> order by a.part_no

> >>

> >> Does not work:

> >>

> >> SELECT a.part_no, a.description, NDF_Onhand

> >> FROM inventory_part_tab a,

> >> (SELECT SUM(qty_onhand) as NDF_Onhand FROM

 inventory_part_location_tab


> >> b

> >>  WHERE b.part_no =a.part_no')

> >> order by a.part_no

> >>

> >> The only difference is the variable reference in the WHERE clause.

> >>

> >> any suggestions?

> >>

> >> On Tue, 23 Jan 2001 11:31:55 GMT, sl_at_xxxsigatl.com (SL) wrote:

> >>

> >> >That could be the problem.  I'm running 8.0.6.0.0.  I thought this

 was


> >> >pretty current.  Is it old?

> >> >

> >> >On Tue, 23 Jan 2001 07:20:46 -0000, "Jonathan Lewis"

> >> ><jonathan_at_jlcomp.demon.co.uk> wrote:

> >> >

> >> >>

> >> >>Which  version of Oracle ?

> >> >>I think this syntax became legal only in 8.1

> >> >>

> >> >>

> >> >>--

> >> >>Jonathan Lewis

> >> >>Yet another Oracle-related web site:

 http://www.jlcomp.demon.co.uk


> >> >>

> >> >>Practical Oracle 8i:  Building Efficient Databases

> >> >>

> >> >>Publishers:  Addison-Wesley

> >> >>See a first review at:

> >> >>http://www.ixora.com.au/resources/index.htm#practical_8i

> >> >>More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html

> >> >>

> >> >>

> >> >>

> >> >>z2124_at_my-deja.com wrote in message

 <94imcv$mhq$1_at_nnrp1.deja.com>...


> >> >>>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

> >> >>>

> >> >>>

> >> >>>Sent via Deja.com

> >> >>>http://www.deja.com/

> >> >>

> >> >>

> >> >

> >>

> >>

> >

> >

> >Sent via Deja.com

> >http://www.deja.com/

>

>




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