Results 1 to 6 of 6
  1. #1
    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,628
    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.
    There are no stupid questions, but there are a LOT of Inquisitive Idiots.

  3. #3
    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: http://www.dbforums.com/oracle/16299...mn-update.html
    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.

  4. #4
    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!

  5. #5
    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)

  6. #6
    Join Date
    Nov 2003
    Posts
    2,817
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •