Your test case runs fine on 9.2.0.4 win2k.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Keith Sauvant" <ne.ws.ksau_at_spamgourmet.com> wrote in message
news:brs92h$rsk$1_at_nets3.rz.RWTH-Aachen.DE...
> Hi Experts,
>
> when sub-selecting a column of a select-result that contains
> - a ROW_NUMBER() OVER() and
> - a LEFT JOIN and
> - a GROUP BY
>
> I get an ORA-03113 (end-of-file on communication channel).
>
> To reproduce:
>
> CREATE TABLE test1 (test1_id int);
> CREATE TABLE test2 (test2_id int, fk_test1_id int);
>
> INSERT INTO test1 VALUES (1);
> INSERT INTO test1 VALUES (2);
>
> SELECT test1_id FROM
> (
> SELECT ROW_NUMBER() OVER( ORDER BY test1.test1_id asc) row_num,
> test1.test1_id
> FROM test1
> LEFT JOIN test2 ON test2.fk_test1_id=test1.test1_id
> GROUP BY test1.test1_id
> )
>
> The query works, if
> - using the inner select only
> - including subselect, but without selecting the ROW_NUMBER()
> - including subselect, but without the LEFT JOIN (!?)
> - including subselect, but without the GROUP BY
> - including subselect, but sub-selecting "*" instead of "test1_id"
>
> Can anybody explain that behaviour?
>
> Thank you in advance and best regards from Aachen, Germany
> Keith
>
Received on Thu Dec 18 2003 - 09:12:26 CST
