Solved

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

Posted on 2009-05-11
13
2,204 Views
Last Modified: 2013-12-07
Hi experts,

             I am getting the error ORA-00998 on attempting to create the following table.
CREATE TABLE SUMMARY AS
select AG1."a_id", S1."s_id", AG1."i_id",
trim(TX1."datetime") , TX2."delta_msec", TX3."del_u",
TX4."con", P1."p_id"
from
AG1, S1, TX1, TX2, 
TX3, P1

Open in new window

0
Comment
Question by:aman0711
  • 7
  • 5
13 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24359420
>trim(TXN_MEASUREMENT."datetime")

you are missing a column name for that.
0
 
LVL 10

Author Comment

by:aman0711
ID: 24359459
hi angell,

                       Please forgive if I am asking too many dumb question. i am very new to this and trying to learn.

   I just put an extra trim for TXN_MEASUREMENT."datetime"

my other columns are like SLOT_META_DATA."slot_id" ... why it claims for a column when I am using datetime
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24359469
I presume you actually want to TRUNC() instead of TRIM(), but that's you to clarify what you need.

the ora error you get can be solved like this:
CREATE TABLE SUMMARY AS
select AG1."a_id", S1."s_id", AG1."i_id",
trim(TX1."datetime") "datetime" , TX2."de_m", TX3."del_u",
TX4."con", P1."p_id"
from
AG1, S1, TX1, TX2, 
TX3, P1

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 24359565
Thanks angel :)

 Actually there was lot of white space in that particular column, so I was trying to trim it up.

Please correct me if I am wrong:-

after using the trim function on a column, I have to again the declare the column name like you did:
trim(TXN_MEASUREMENT."datetime") "datetime

right?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24361257
yes
0
 
LVL 10

Author Closing Comment

by:aman0711
ID: 31580341
Thanks :)
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: 25852840
Code to be replaced in the question body:



CREATE TABLE SUMMARY AS
select AG1."a_id", S1."s_id", AG1."i_id",
trim(TX1."datetime") , TX2."delta_msec", TX3."del_u",
TX4."con", P1."p_id"
from
AG1, S1, TX1, TX2, 
TX3, P1

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 25852848
Morphed code for ID: 24359469

CREATE TABLE SUMMARY AS
select AG1."a_id", S1."s_id", AG1."i_id",
trim(TX1."datetime") "datetime" , TX2."de_m", TX3."del_u",
TX4."con", P1."p_id"
from
AG1, S1, TX1, TX2, 
TX3, P1

Open in new window

0
 
LVL 10

Author Comment

by:aman0711
ID: 25854732
Hi Angel,

                Thanks for helping me out with this issue. Your comment made me confused here, I was actually following the way you are suggesting to edit a question (Post code in the Request Attention page ), but then I was told that, thats the wrong way of doing it and I have to post the code here itself.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 25854901
As you where told here:
http://www.experts-exchange.com/Community_Support/General/Q_24904088.html

>Usually, you go into the related question, and press "Request Attention" there (exclamation mark button). This provides us with the link to this question.

in the RA text, you say you want to change some code snippets
you will find the relevant CS question here (in your profile):
http://www.experts-exchange.com/QH_4650528.html

you can then there post the "new" code.

a3
0
 
LVL 10

Author Comment

by:aman0711
ID: 25855001
OH thanks :)

 I will follow this process now.

What will happen to all the changes I requested already, Angel?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 25855051
you have indeed requested plenty of those.
from what I read in the admin comments of those questions, they will be processed.
one by one.

you really put a lot of work for the admins last night ... ;/

a3
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
compre toata in where clue oracle 4 56
null value 15 94
Concat multi row values of a field in oracle 6 56
selective queries 7 21
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

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

13 Experts available now in Live!

Get 1:1 Help Now