Solved

ORA-00922: missing or invalid option

Posted on 2009-05-11
7
475 Views
Last Modified: 2013-12-18
HI Experts,

                   I am trying to create a table which will contain the columns from multiple different tables.

           The query I am using is below, On executing it, I am getting the error 00922. Where am I making the mistake?
create table SUMMARY 8 tablespace vsland_dat_128m AS ( SELECT AGENT_META_DATA."agent_id", SELECT SLOT_META_DATA."slot_id",

SELECT AGENT_META_DATA."instance_id", SELECT TXN_MEASUREMENT."datetime",

SELECT TXN_PAGE_PERFORMANCE."delta_msec", SELECT TXN_PAGE_PERFORMANCE."delta_user_msec",

SELECT TXN_PAGE_STATUS."content_errors", SELECT PROFILE_META_DATA."profile_id"

FROM

AGENT_META_DATA, SLOT_META_DATA, TXN_MEASUREMENT, TXN_PAGE_PERFORMANCE, TXN_PAGE_STATUS,

PROFILE_META_DATA

)

Open in new window

0
Comment
Question by:aman0711
  • 4
  • 3
7 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 24359132
your should be like the one below. But your select query returns the cross product of all the tables. If this is that you want then no problem, but if you want some join conditions than you should add them to your select query.

CREATE TABLE vsland_dat_128m AS  

SELECT AGENT_META_DATA."agent_id", SLOT_META_DATA."slot_id",

	AGENT_META_DATA."instance_id", TXN_MEASUREMENT."datetime",

	TXN_PAGE_PERFORMANCE."delta_msec", TXN_PAGE_PERFORMANCE."delta_user_msec",

	TXN_PAGE_STATUS."content_errors",PROFILE_META_DATA."profile_id"

FROM

	AGENT_META_DATA, SLOT_META_DATA, TXN_MEASUREMENT, TXN_PAGE_PERFORMANCE, TXN_PAGE_STATUS,

	PROFILE_META_DATA

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 24359151
Hi Tigin,

                  Thanks for quick response. I tried your solution and still getting the same error.

CREATE TABLE SUMMARY vsland_dat_128m AS  

SELECT AGENT_META_DATA."agent_id", SLOT_META_DATA."slot_id",

        AGENT_META_DATA."instance_id", TXN_MEASUREMENT."datetime",

        TXN_PAGE_PERFORMANCE."delta_msec", TXN_PAGE_PERFORMANCE."delta_user_msec",

        TXN_PAGE_STATUS."content_errors",PROFILE_META_DATA."profile_id"

FROM

        AGENT_META_DATA, SLOT_META_DATA, TXN_MEASUREMENT, TXN_PAGE_PERFORMANCE, TXN_PAGE_STATUS,

        PROFILE_META_DATA

Open in new window

0
 
LVL 26

Expert Comment

by:tigin44
ID: 24359199
before creating the table be sure that the select statement returns the correct data set that you want. After then add the create table as part to your statement. Since I dont have your table scripts I write the code over the head. Check the select part first.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Author Comment

by:aman0711
ID: 24359208
ok cool, will do :)
0
 
LVL 10

Author Comment

by:aman0711
ID: 24359306
Hi tigin,


             This is the select query which is working perfectly fine. but as soon as I add, create table summary as in the beginning, it starts giving errors



(select AGENT_META_DATA."agent_id", SLOT_META_DATA."slot_id", AGENT_META_DATA."instance_id",

trim(TXN_MEASUREMENT."datetime") , TXN_PAGE_PERFORMANCE."delta_msec", TXN_PAGE_PERFORMANCE."delta_user_msec",

TXN_PAGE_STATUS."content_errors", PROFILE_META_DATA."profile_id"

from AGENT_META_DATA, SLOT_META_DATA, TXN_MEASUREMENT, TXN_PAGE_PERFORMANCE, 

TXN_PAGE_STATUS, PROFILE_META_DATA)

Open in new window

0
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
ID: 24359341
your syntax should  be like the one attached without paranthesis
create table summary as

select AGENT_META_DATA."agent_id", SLOT_META_DATA."slot_id", AGENT_META_DATA."instance_id",

trim(TXN_MEASUREMENT."datetime") , TXN_PAGE_PERFORMANCE."delta_msec", TXN_PAGE_PERFORMANCE."delta_user_msec",

TXN_PAGE_STATUS."content_errors", PROFILE_META_DATA."profile_id"

from AGENT_META_DATA, SLOT_META_DATA, TXN_MEASUREMENT, TXN_PAGE_PERFORMANCE, 

TXN_PAGE_STATUS, PROFILE_META_DATA

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 24359365
Sorry for bothering you again and again :)
I am using that without the paranthesis... but getting the follwowing:


ORA-00998: must name this expression with a column alias
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL query of Oracle 10g database. 8 79
Convert Oracle data into XML document 2 59
Read only access to a Procedure in oracle? 4 48
selective queries 7 22
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now