R: Oracle Subquery Question

I am quite curious about the hint "no_merge". Do you suggest it for queries stuctured like this one?

Thanks

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message 980281177.24097.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> 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
> WHERE b.part_no =a.part_no
> order by a.part_no
>
> 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>...
> >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 Thu Feb 08 2001 - 12:59:38 CST