DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Merge Ora-00904

  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Merge Ora-00904

    I have an ORA-00904 oracle error when I run the following merge sql statement. I don't understand why??? If anyone can help me, many thanks.

    MERGE INTO TRAG_NODE_HIE_PRD@r_nrsp_f F USING MRAG_NODE_HIE_PRD FDS
    ON ( F.NPR_PHI_FDS_ID = FDS.NPR_PHI_FDS_ID
    AND F.NPR_OIN_INDEX = FDS.NPR_OIN_INDEX
    AND F.NPR_PHI_ID = FDS.NPR_PHI_ID
    AND F.NPR_PRH_ID = FDS.NPR_PRH_ID
    )
    WHEN MATCHED THEN UPDATE SET F.NPR_CHAR_TYPE1 = FDS.NPR_CHAR_TYPE1
    , F.NPR_CHAR_TYPE10 = FDS.NPR_CHAR_TYPE10
    , F.NPR_CHAR_TYPE2 = FDS.NPR_CHAR_TYPE2
    , F.NPR_CHAR_TYPE3 = FDS.NPR_CHAR_TYPE3
    , F.NPR_CHAR_TYPE4 = FDS.NPR_CHAR_TYPE4
    , F.NPR_CHAR_TYPE5 = FDS.NPR_CHAR_TYPE5
    , F.NPR_CHAR_TYPE6 = FDS.NPR_CHAR_TYPE6
    , F.NPR_CHAR_TYPE7 = FDS.NPR_CHAR_TYPE7
    , F.NPR_CHAR_TYPE8 = FDS.NPR_CHAR_TYPE8
    , F.NPR_CHAR_TYPE9 = FDS.NPR_CHAR_TYPE9
    , F.NPR_CHAR_VALUE1 = FDS.NPR_CHAR_VALUE1
    , F.NPR_CHAR_VALUE10 = FDS.NPR_CHAR_VALUE10
    , F.NPR_CHAR_VALUE2 = FDS.NPR_CHAR_VALUE2
    , F.NPR_CHAR_VALUE3 = FDS.NPR_CHAR_VALUE3
    , F.NPR_CHAR_VALUE4 = FDS.NPR_CHAR_VALUE4
    , F.NPR_CHAR_VALUE5 = FDS.NPR_CHAR_VALUE5
    , F.NPR_CHAR_VALUE6 = FDS.NPR_CHAR_VALUE6
    , F.NPR_CHAR_VALUE7 = FDS.NPR_CHAR_VALUE7
    , F.NPR_CHAR_VALUE8 = FDS.NPR_CHAR_VALUE8
    , F.NPR_CHAR_VALUE9 = FDS.NPR_CHAR_VALUE9
    , F.NPR_CHAR_1 = FDS.NPR_CHAR_1
    , F.NPR_CHAR_10 = FDS.NPR_CHAR_10
    , F.NPR_CHAR_2 = FDS.NPR_CHAR_2
    , F.NPR_CHAR_3 = FDS.NPR_CHAR_3
    , F.NPR_CHAR_4 = FDS.NPR_CHAR_4
    , F.NPR_CHAR_5 = FDS.NPR_CHAR_5
    , F.NPR_CHAR_6 = FDS.NPR_CHAR_6
    , F.NPR_CHAR_7 = FDS.NPR_CHAR_7
    , F.NPR_CHAR_8 = FDS.NPR_CHAR_8
    , F.NPR_CHAR_9 = FDS.NPR_CHAR_9
    , F.NPR_CHILD_COUNT = FDS.NPR_CHILD_COUNT
    , F.NPR_INFACT_DESCRIPTION = FDS.NPR_INFACT_DESCRIPTION
    , F.NPR_IS_PRIVATE = FDS.NPR_IS_PRIVATE
    , F.NPR_LEVEL_FROM_TOP = FDS.NPR_LEVEL_FROM_TOP
    , F.NPR_LEVEL_NODE_CHAR = FDS.NPR_LEVEL_NODE_CHAR
    , F.NPR_LONG_DESCRIPTION = FDS.NPR_LONG_DESCRIPTION
    , F.NPR_NPR_PHI_FDS_ID = FDS.NPR_NPR_PHI_FDS_ID
    , F.NPR_NPR_PHI_ID = FDS.NPR_NPR_PHI_ID
    , F.NPR_NPR_PRH_ID = FDS.NPR_NPR_PRH_ID
    , F.NPR_OIN_ORD_ID = FDS.NPR_OIN_ORD_ID
    , F.NPR_PS_ID = FDS.NPR_PS_ID
    , F.NPR_SHORT_DESCRIPTION = FDS.NPR_SHORT_DESCRIPTION
    , F.NPR_SINGLE_DESCRIPTION = FDS.NPR_SINGLE_DESCRIPTION
    , F.NPR_TAG = FDS.NPR_TAG
    WHEN NOT MATCHED THEN INSERT (F.NPR_PHI_FDS_ID
    ,F.NPR_OIN_INDEX
    ,F.NPR_PHI_ID
    ,F.NPR_PRH_ID
    ,F.NPR_CHAR_TYPE1
    ,F.NPR_CHAR_TYPE10
    ,F.NPR_CHAR_TYPE2
    ,F.NPR_CHAR_TYPE3
    ,F.NPR_CHAR_TYPE4
    ,F.NPR_CHAR_TYPE5
    ,F.NPR_CHAR_TYPE6
    ,F.NPR_CHAR_TYPE7
    ,F.NPR_CHAR_TYPE8
    ,F.NPR_CHAR_TYPE9
    ,F.NPR_CHAR_VALUE1
    ,F.NPR_CHAR_VALUE10
    ,F.NPR_CHAR_VALUE2
    ,F.NPR_CHAR_VALUE3
    ,F.NPR_CHAR_VALUE4
    ,F.NPR_CHAR_VALUE5
    ,F.NPR_CHAR_VALUE6
    ,F.NPR_CHAR_VALUE7
    ,F.NPR_CHAR_VALUE8
    ,F.NPR_CHAR_VALUE9
    ,F.NPR_CHAR_1
    ,F.NPR_CHAR_10
    ,F.NPR_CHAR_2
    ,F.NPR_CHAR_3
    ,F.NPR_CHAR_4
    ,F.NPR_CHAR_5
    ,F.NPR_CHAR_6
    ,F.NPR_CHAR_7
    ,F.NPR_CHAR_8
    ,F.NPR_CHAR_9
    ,F.NPR_CHILD_COUNT
    ,F.NPR_INFACT_DESCRIPTION
    ,F.NPR_IS_PRIVATE
    ,F.NPR_LEVEL_FROM_TOP
    ,F.NPR_LEVEL_NODE_CHAR
    ,F.NPR_LONG_DESCRIPTION
    ,F.NPR_NPR_PHI_FDS_ID
    ,F.NPR_NPR_PHI_ID
    ,F.NPR_NPR_PRH_ID
    ,F.NPR_OIN_ORD_ID
    ,F.NPR_PS_ID
    ,F.NPR_SHORT_DESCRIPTION
    ,F.NPR_SINGLE_DESCRIPTION
    ,F.NPR_TAG
    )
    VALUES (FDS.NPR_PHI_FDS_ID
    ,FDS.NPR_OIN_INDEX
    ,FDS.NPR_PHI_ID
    ,FDS.NPR_PRH_ID
    ,FDS.NPR_CHAR_TYPE1
    ,FDS.NPR_CHAR_TYPE10
    ,FDS.NPR_CHAR_TYPE2
    ,FDS.NPR_CHAR_TYPE3
    ,FDS.NPR_CHAR_TYPE4
    ,FDS.NPR_CHAR_TYPE5
    ,FDS.NPR_CHAR_TYPE6
    ,FDS.NPR_CHAR_TYPE7
    ,FDS.NPR_CHAR_TYPE8
    ,FDS.NPR_CHAR_TYPE9
    ,FDS.NPR_CHAR_VALUE1
    ,FDS.NPR_CHAR_VALUE10
    ,FDS.NPR_CHAR_VALUE2
    ,FDS.NPR_CHAR_VALUE3
    ,FDS.NPR_CHAR_VALUE4
    ,FDS.NPR_CHAR_VALUE5
    ,FDS.NPR_CHAR_VALUE6
    ,FDS.NPR_CHAR_VALUE7
    ,FDS.NPR_CHAR_VALUE8
    ,FDS.NPR_CHAR_VALUE9
    ,FDS.NPR_CHAR_1
    ,FDS.NPR_CHAR_10
    ,FDS.NPR_CHAR_2
    ,FDS.NPR_CHAR_3
    ,FDS.NPR_CHAR_4
    ,FDS.NPR_CHAR_5
    ,FDS.NPR_CHAR_6
    ,FDS.NPR_CHAR_7
    ,FDS.NPR_CHAR_8
    ,FDS.NPR_CHAR_9
    ,FDS.NPR_CHILD_COUNT
    ,FDS.NPR_INFACT_DESCRIPTION
    ,FDS.NPR_IS_PRIVATE
    ,FDS.NPR_LEVEL_FROM_TOP
    ,FDS.NPR_LEVEL_NODE_CHAR
    ,FDS.NPR_LONG_DESCRIPTION
    ,FDS.NPR_NPR_PHI_FDS_ID
    ,FDS.NPR_NPR_PHI_ID
    ,FDS.NPR_NPR_PRH_ID
    ,FDS.NPR_OIN_ORD_ID
    ,FDS.NPR_PS_ID
    ,FDS.NPR_SHORT_DESCRIPTION
    ,FDS.NPR_SINGLE_DESCRIPTION
    ,FDS.NPR_TAG
    )

    In attachment, you have the description of tables
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Post the complete error
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Aug 2006
    Posts
    4
    Here the complete error

    SQL> @d:\temp\1.sql
    AND F.NPR_OIN_INDEX = FDS.NPR_OIN_INDEX
    *
    ERREUR Ã* la ligne 3 :
    ORA-00904: "FDS"."NPR_OIN_INDEX": invalid identifier

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    What is your version... See the bug: 3413826 .. while merging over DB Link.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Aug 2006
    Posts
    4
    I work on Oracle9i Enterprise Edition Release 9.2.0.7.0
    I'm going to see the bug : 3413826

  6. #6
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    And this bug is resolved in 10.2
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I've also read of problems when attempting to update columns used in the join specification also. you might like toremove them since they're also redundant anyway.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Yes, columns in ON clause cannot be updated. And he is not doing it anyway.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

Posting Permissions

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


Click Here to Expand Forum to Full Width