Re: Oracle Subquery Question

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/ Received on Tue Jan 23 2001 - 07:48:24 CST