Connection closed on simple query

From: Caspar von Seckendorff <seckendorff_at_alphatec.de>
Date: 12 Jun 2003 07:01:31 -0700
Message-ID: <5b031a3f.0306120601.36adc33b@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; Received on Thu Jun 12 2003 - 09:01:31 CDT