Re: Temporary work tables (a beginner's question)

This may be real stupid question but can't we do views? or am I missing something here?

Sunil

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
 

-- 
================================================================
Sunil Godithi
Email:    sunil_at_ameya.com	
Web Page: http://208.134.23.100/

Wastin' away again in Margaritaville,
Lookin' for my lost shaker of salt.
Some people claim that there's a woman to blame, but I know,
It's all my own fault... 
================================================================