seckendorff_at_alphatec.de (Caspar von Seckendorff) wrote in message news:<5b031a3f.0306120601.36adc33b_at_posting.google.com>...
> On Oracle 9.2i I get the error ORA-03113 when executing the following
> script. I actually wrote the following example for analysing a more
> complex query, but then I realized that I can't execute it - Oracle
> just simply closes the connection.
>
> Is this a bug? How can I work around it?
>
> Greetings,
>
> -Caspar
>
>
> CREATE TABLE test_table (
> country_id NUMBER(10),
> country_text CHAR(20),
> turnover NUMBER(10),
> t_year NUMBER(4)
> );
>
> INSERT INTO test_table (country_id, country_text, turnover, t_year)
> VALUES (1, 'USA', 300, 1999);
>
> INSERT INTO test_table (country_id, country_text, turnover, t_year)
> VALUES (1, 'USA', 300, 2000);
>
> INSERT INTO test_table (country_id, country_text, turnover, t_year)
> VALUES (1, 'USA', 300, 2001);
>
> INSERT INTO test_table (country_id, country_text, turnover, t_year)
> VALUES (2, 'Germany', 300, 2000);
>
> INSERT INTO test_table (country_id, country_text, turnover, t_year)
> VALUES (2, 'Germany', 300, 2001);
>
> SELECT
> COALESCE(t00.country_text, t01.country_text),
> t00.turnover,
> t01.turnover
> FROM (
> SELECT
> country_id,
> MAX(country_text) AS country_text,
> SUM(turnover) AS turnover
> FROM test_table
> WHERE t_year = 1999
> GROUP BY country_id
> ) t00 FULL JOIN (
> SELECT
> country_id,
> MAX(country_text) AS country_text,
> SUM(turnover) AS turnover
> FROM test_table
> WHERE t_year = 2000
> GROUP BY country_id
> ) t01 ON t00.country_id = t01.country_id
> ORDER BY 1;
I tried the same scenario on (Oracle 9.2.0.1.0 on Win2K SP2) and got a
core dump with ORA-3113. However, if you change FULL JOIN to either
JOIN, LEFT/RIGHT OUTER JOIN, CROSS JOIN, then it works OK, but
ofcourse resultset is not the same. You may want to open an iTAR on
metalink... as already suggested.
Regards
/Rauf Sarwar
Received on Mon Jun 16 2003 - 15:13:26 CDT
