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
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
What is your version... See the bug: 3413826 .. while merging over DB Link.
I work on Oracle9i Enterprise Edition Release 9.2.0.7.0
I'm going to see the bug : 3413826
And this bug is resolved in 10.2
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.
Yes, columns in ON clause cannot be updated. And he is not doing it anyway.
Posting Permissions
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
Forum Rules
Click Here to Expand Forum to Full Width