| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Oracle Subquery Question
Perhaps you're after something like:
SELECT a.part_no, a.description, NDF_Onhand FROM
inventory_part_tab a,
(
SELECT /*+ no_merge */
part_no, SUM(qty_onhand) as NDF_Onhand
FROM inventory_part_location_tab
group by part_no
) b
In 8.0, you can put any __standalone__ query in place of a single table, but can't include a reference to a table in the surrounding FROM clause.
-- 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 sl_at_xxxsigatl.com wrote in message <3a6d80ad.1502264_at_news.supernews.com>...Received on Tue Jan 23 2001 - 14:02:47 CST
>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/
>>>
>>>
>>
>
 |
 |