Re: Oracle Subquery Question

From: Greg Vitetzakis <greg_at_ostnet.com>
Date: Fri, 9 Feb 2001 17:36:59 -0500
Message-ID: <3a84718b@news1>

Try This...(I'm assuming you only want one record back for the Specific Part_No)

SELECT a.part_no, a.description, NDF_Onhand FROM inventory_part_tab a,
 (SELECT PART_NO, SUM(qty_onhand) as NDF_Onhand

     FROM inventory_part_location_tab b
     WHERE b.part_no = '11532'
     GROUP BY PART_NO) BB

 WHERE A.PART_NO = BB.PART_NO
 order by a.part_no

Hope this helps

Greg Vitetzakis

"Steve Slade" <steve.j.slade_at_bt.com> wrote in message news:94k5j7$7ue$1_at_pheidippides.axion.bt.co.uk...
> It might just be a typo in the reply, but you have a single quote on the
> line :
>
> WHERE b.part_no =a.part_no')
>
> should this be:
>
> WHERE b.part_no =a.part_no)
>
> rgds
>
>
> 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/
> > >>
> > >>
> > >
>
Received on Fri Feb 09 2001 - 16:36:59 CST