Sql-error: Ora-00904:

marcopo · #1 (**permalink**) 01-02-13, 10:48

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

anacedent · #2 (**permalink**) 01-02-13, 11:00

post results from below

desc INFODB.RORC

flyboy · #3 (**permalink**) 01-02-13, 11:06

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.

marcopo · #4 (**permalink**) 01-02-13, 12:10

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!

flyboy · #5 (**permalink**) 01-03-13, 02:27

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)

shammat · #6 (**permalink**) 01-03-13, 04:07

Quote:

Originally Posted by marcopo

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech