Re: Oracle Subquery Question

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 Tue Jan 23 2001 - 07:05:26 CST