Re: SQL frustration - cannot make "obvious" query work well

What's in a namespace wrote:

> <giuliano.procida_at_googlemail.com> schreef in bericht
> news:1164890406.726998.236320_at_j44g2000cwa.googlegroups.com...
> > SELECT slice, value
> > FROM
> > all_slices,
> > (
> > SELECT slice, value
> > FROM (
> > SELECT slice, value
> > FROM points
> > WHERE slice = all_slices.slice
> > AND x <= :x
> > AND y <= :y
> > ORDER BY y DESC, x DESC
> > )
> > WHERE ROWNUM = 1
> > )
> >
> > -- ORA-00904: "ALL_SLICES"."SLICE": invalid identifier
> >
> > The nested, nested query cannot see the top-level table. This is very
> > annoying.
> >
> > I have tried:
> >
> > a) Getting around the 00904 limitation somehow. No luck. This exact
> > issue has popped up to asktom etc. a few times, no solution seems
> > available.
> >
> Looks like you could solve the nesting with a normal join.....

Really? It would excellent if that were so.

There are 3 levels to the query above:

1. outer, can see bind variables and 1.
2. middle, can see bind variables and 1. and 2.
3. inner, can see bind variables and 2. and 3.

So I can only add an item by adding it to level 3, 2 or as a bind variable. A bind-variable brings us back to a single slice, level 3 itself can do nothing useful. This leaves level 2.

Anything added to level 2 will break the rownum as far as I can tell. Rownum needs to be reset to 1 per slice (i.e., in a subquery) for this query structure to work.

Giuliano. Received on Thu Nov 30 2006 - 08:39:13 CST