How do I use temp tables in Oracle8i? 2

[mheilbut]

I was used to using the following query:

Select name,sum(sal) as sum from emp
where date_emp >= "01/01/2003"
group by name
into temp aaa

Select * from aaa
where sum >= 5000

The queries above are just examples. My question is, how do I use this temp table aaa in oracle? It doesn't accept the above statements... of course, I need a more complex query than the above to use it in.

Thanx, Michel.

Michel, São Paulo, Brazil

 

[Scunningham99]

Ive not seen it done that way b4.

try

insert into temp (aaa) values (select sum(sal) from emp
where data_emp>="01/01/2003"
group by name

im assuming aaa is the name of the column in table temp!

HTH



Sy UK

 

[Scunningham99]

Ive not seen it done that way b4.

try

insert into temp (aaa) values (select sum(sal) from emp
where data_emp>="01/01/2003&quot
group by name

im assuming aaa is the name of the column in table temp!

HTH



Sy UK

 

[Scunningham99]

Ive not seen it done that way b4.

try

insert into temp (aaa) values (select sum(sal) from emp
where data_emp>="01/01/2003"
group by name)

im assuming aaa is the name of the column in table temp!

HTH



Sy UK

 

[sybaseguru]

In Oracle temporary tables are permanent and data is temporary (on exit or commit). So what you need to do is to create the temp table first

create global temporary table aaa
(
c1 number,
c2 number
)
on commit delete/preserve rows;  -- note the options
insert into aaa
Select name,sum(sal) as sum from emp
where date_emp >= "01/01/2003"
group by name;
Select * from aaa
where sum >= 5000;

Hope this helps

 

[Scunningham99]

sorry would have mentioned global temporary tables, but i thought they were only relative to 9i



Sy UK

 

[Scunningham99]

sorry would have mentioned global temporary tables, but i thought they were only relative to 9i. My Mistake


Sy UK

 

[louisas]

Thanks sybaseguru now I know how to use temporary tables in Oracle coming from SQL server background.

Louisa

 

[Scunningham99]

sorry... I would have mentioned global temporary tables, but i thought they were only relative to 9i. My Mistake!!

On commit preserve rows means that the rows will be kept in table, and only held for the session.

on commit delete rows means exactly what it says

HTH


Sy UK

 

[mheilbut]

Thanx a lot all of you!
I think sybaseguru's answer was especially clarifying.
My question was if you could use a temp which would be created without actually using the create table, and automatically dropped after loggin off the session.
As I see, this is not possible, but now I know how it is in Oracle.

Thanx.

 

[sem]

Michel, a small note: you can not use "sum(sal) as sum" in Oracle, because sum word is reserved.

Scunningham99, values clause is redundant (and inappropriate) if inserting from subquery.


Regards, Dima