ORACLE does not support temporary tables (in the SQL92 meaning of the
term), only temporary tablespaces (and only starting with 7.3.2, I
believe). Temporary tablespaces are meant to improve performance and not
for SQL92 compliance.
You can try implementing your query in ORACLE directly like this:
SELECT t1.state, t1.city, "SUM1", "SUM2"
FROM hometest t1, (select state, city, COUNT(*) "SUM1" from hometest
WHERE age> 21 GROUP BY state, city) t2, (select state, city, COUNT(*)
"SUM2" from hometest WHERE income>50000 GROUP BY state, city) t3
WHERE t1.state=t2.state
AND t1.state=t3.state
AND t1.city=t2.city
AND t1.city=t3.city
You may need a DISTINCT clause on the very first SELECT; it's been a
while since I ran this kind of queries.
Hope this helps.
Michael Serbanescu
David Bradford wrote:
>
> 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 Thu Jul 03 1997 - 00:00:00 CDT
