If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Sql-error: Ora-00904:

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 4
Sql-error: Ora-00904:

Hello Community,

I getting always the same error.

SQL-ERROR: ORA-00904: "RORC"."DATETIME1":
00904. 00000 - "%s: invalid identifier".

I have to insert values in an permanent table. The values I want to insert are from a view (analyse).

The code I used.
Code:
INSERT into INFODB.RORC (TIME_TRADE, DAY_TRADE)
select 'TIME_TRADE','DAY_TRADE' 
From analyse
where rorc.tradeid = analyse.tradeid AND rorc.datetime1 = analyse.datetime1;
Best Regards

Marco
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 7,597
post results from below

desc INFODB.RORC
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
Don't say, show. Don't promise, prove.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 615
Hi,
maybe you should get familiar with basic database terms firstly.

INSERT means to, well, insert entirely new row.
UPDATE means to change values in one or more columns of existing rows.

It seems to me that your "insert values" in fact means the second case.
You may find some examples e.g. in this thread: set one column to another table's column[Update]
or on AskTom: http://asktom.oracle.com/pls/asktom/...:1197999096334

What shall happen when there is no corresponding row in ANALYSE table (shall the columns be updated to NULL or shall be their value preserved)?

By the way, you are "inserting" string literals 'TIME_TRADE' and 'DAY_TRADE' now. It could be done without query to ANALYSE table at all.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 4
Ok thanks, With UPDATE it is working. I just wanted to use INSERT because the performance of UPDATE should not be the best.

Thank you!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2007
Posts: 615
Well, if you want to use INSERT, do it, but do not be surprised when the code functionality changes. I thought that fulfilling requirements is always the most important thing, but maybe you are an exception from this rule.

Of course, UPDATE may be very slow on very large tables with insufficient PCTFREE to hold new values. In that case, you may be interested in reading this aticle on AskTom: http://asktom.oracle.com/pls/asktom/...6407993912330:
(note, that in the end, the result is the same as would be done with UPDATE statement, although table data may be unavailable for some time)
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Posts: 2,806
Quote:
Originally Posted by marcopo View Post
the performance of UPDATE should not be the best.
A vague statement like "should not be the best" is not something you should use as the base for your decisions on how you implement something.

Implement your required functionality and only if you have performance problems in your workload, take action to improve that.

An UPDATE using a primary key will be extremely fast, even when the table has several millions of rows.

Premature optimization - especially if it isn't based on hard facts and your own testing - is seldom a good idea.
__________________
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

Tips for good questions:

http://tkyte.blogspot.de/2005/06/how...questions.html
http://wiki.postgresql.org/wiki/SlowQueryQuestions
http://catb.org/esr/faqs/smart-questions.html
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On