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

Use this syntax:

create table count1 as (

        SELECT STATE, CITY,  1 AS COUNT1, 0 AS COUNT2
        FROM HOMETEST
        WHERE AGE > 21)

create table count2 as (
        SELECT STATE, CITY,  0 AS COUNT1, 1 AS COUNT2
        FROM HOMETEST
        WHERE INCOME > 50000)

After that the union query should work fine

--
Tom Mettling
W.T. Chen & Company, Inc.
mettlingt_at_wtchen.com




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 ............................etc