Re: Oracle Subquery Question

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/
Received on Tue Jan 23 2001 - 08:09:26 CST