Temporary work tables (a beginner's question)

From: David Bradford <mitimktg_at_shore.net>
Date: 1997/07/02
Message-ID: <33BABFBD.3316@shore.net>#1/1

Hi,

We're doing an evaluation of Oracle 7.3 against SQL Server and Sybase, and are running into a snag. The other two let us form a select statement and store the results in a temporary table, e.g.

SELECT STATE, CITY, 1 AS COUNT1, 0 AS COUNT2

	INTO #COUNT1 FROM HOMETEST 
	WHERE AGE > 21

and

SELECT STATE, CITY, 0 AS COUNT1, 1 AS COUNT2

	INTO #COUNT2 FROM HOMETEST 
	WHERE INCOME > 50000

and then UNION the resulting tables giving us counts within cities for both age groups and income

SELECT state, CITY, SUM(COUNT1) SUM1, SUM(COUNT2) SUM2

	INTO #COUNT4
	FROM #COUNT1
	GROUP BY state, CITY

UNION
SELECT state, CITY, SUM(COUNT1) SUM1, SUM(COUNT2) SUM2

	FROM #COUNT2
	GROUP BY state, CITY

Temporary table #COUNT4 will have everything we need for reporting.

Oracle does not include the INTO sub-clause in its SELECT statement.

How do we accomplish this directly in Oracle. I know we can use the "CREATE TABLE AS subquery" command, but then we have to do extra management on our tablespaces.

I guess the real question we're asking is if there is any provision for work files within Oracle's design.

Thanks in advance.

Dave Received on Wed Jul 02 1997 - 00:00:00 CDT