468,753 Members | 1,123 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,753 developers. It's quick & easy.

Creating global temporary table on join condition

Hi,
Is there a way in which i can create a global temporary table based on join condition
eg:

scott@ISNS>CREATE GLOBAL TEMPORARY TABLE x
2 AS
3 SELECT deptno,dname
4 FROM(
5 select e.deptno,d.dname
6 from emp e ,dept d
7 where e.deptno=d.deptno
8 MINUS
9 select d.deptno,d.dname
10 from dept d
11 )
12 ON COMMIT PRESERVE ROWS;
ON COMMIT PRESERVE ROWS
*
ERROR at line 12:
ORA-00933: SQL command not properly ended


as you can see i am getting the error shown.

Do reply to :
Senthil Kumar.V
Mail id Removed
Nov 2 '07 #1
4 2283
amitpatel66
2,367 Expert 2GB
Hi,
Is there a way in which i can create a global temporary table based on join condition
eg:

scott@ISNS>CREATE GLOBAL TEMPORARY TABLE x
2 AS
3 SELECT deptno,dname
4 FROM(
5 select e.deptno,d.dname
6 from emp e ,dept d
7 where e.deptno=d.deptno
8 MINUS
9 select d.deptno,d.dname
10 from dept d
11 )
12 ON COMMIT PRESERVE ROWS;
ON COMMIT PRESERVE ROWS
*
ERROR at line 12:
ORA-00933: SQL command not properly ended

as you can see i am getting the error shown.
Remove the last line, it will get created. Its not the problem of using a JOIN while creating temporary table
Nov 2 '07 #2
amitpatel66
2,367 Expert 2GB
This is how u do if you want to include ON COMMIT statement:
Expand|Select|Wrap|Line Numbers
  1. CREATE GLOBAL TEMPORARY TABLE x ON COMMIT DELETE ROWS AS (SELECT * from emp)
  2.  
Nov 2 '07 #3
This is how u do if you want to include ON COMMIT statement:
Expand|Select|Wrap|Line Numbers
  1. CREATE GLOBAL TEMPORARY TABLE x ON COMMIT DELETE ROWS AS (SELECT * from emp)
  2.  
Thanks for your reply amit.
Nov 5 '07 #4
amitpatel66
2,367 Expert 2GB
Thanks for your reply amit.

You are Welcome :)

Amit
Nov 6 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Yannick Turgeon | last post: by
6 posts views Thread by davegb | last post: by
13 posts views Thread by Nagib Abi Fadel | last post: by
reply views Thread by dataguy | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.