473,394 Members | 1,036 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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 2623
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

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

Similar topics

5
by: Jim Garrison | last post by:
Scenario: 1) Create a GLOBAL TEMPORARY table and populate it with one (1) row. 2) Join that table to another with about 1 million rows. The join condition selects a few hundred rows. ...
11
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the...
2
by: Yannick Turgeon | last post by:
Hello all, I'm using SS2K on W2K. Brieffing: Many months ago, I created a stored procedure only used by those with admin rights in SS. Now, someone else (without admin rights) has to run it....
10
by: Ranga | last post by:
I was unable to run the statement "CREATE GLOBAL TEMPORARY TABLE" on unix version of DB2, it gave the follwing error db2 => create global temporary table temp ( OGI_SYS_NR char(8) ) DB21034E ...
6
by: davegb | last post by:
I'm trying to create a self-join table to show the relationship between employee and supervisor. In another thread, I was advised to create a SupervisorID in the employee table, a separate...
13
by: Nagib Abi Fadel | last post by:
Is it possible to create a session variable for each user in Postresql ?? Thx
5
by: das | last post by:
hello all, this might be simple: I populate a temp table based on a condition from another table: select @condition = condition from table1 where id=1 in my stored procedure I want to do...
0
by: dataguy | last post by:
I have my developers explaining the stored procedures that they write using visual studio. These stored procedures are for DB2 os390. In one case, one of the developers has defined a global...
0
by: santoshsri | last post by:
Hi, I want to create a temporary table in my ASP code to manipulate the data coming from DB2. I am using SQL = Declare Global Temporary table session.temp1 ( Id Decimal (12) , Name...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.