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

<giuliano.procida_at_googlemail.com> schreef in bericht news:1164897553.386874.93640_at_j44g2000cwa.googlegroups.com...
> 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.
>

I'm afraid you're right (and I'm wrong...) Missed the rownum. Received on Thu Nov 30 2006 - 12:12:39 CST