请求协助排查Merge语句中的ORA-00920:无效关系运算符错误

First off, let's pinpoint the immediate cause of your error—there's a typo with an accidental space in your subquery that's breaking the relational operator logic:

In the line:

AND RT.EFFDT = (SELECT MAX(EFFDT) FROM PS_CTS_IQN_BU_RATE RT1 WHERE RT.BUSIN ESS_UNIT = RT1.BUSINESS_UNIT AND RT1.EFFDT <= sysdate )

You have RT.BUSIN ESS_UNIT (with an unintended space between BUSIN and ESS_UNIT) instead of the correct column name RT.BUSINESS_UNIT. This invalid column reference confuses the SQL parser, leading directly to the ORA-00920 error.

Fixed MERGE Statement #

Here's the corrected version with that typo fixed, plus improved formatting for readability:

MERGE INTO PS_CT_IQN_ACC_STG STG 
USING PS_CTS_IQN_BU_RATE RT 
ON (STG.BUSINESS_UNIT = RT.BUSINESS_UNIT) 
WHEN MATCHED THEN UPDATE 
SET 
    STG.STANDARD_RATE = RT.STANDARD_RATE, 
    STG.STANDARD_HOURS = RT.HOURS_PER_DAY, 
    STG.CURRENCY_CD = RT.CURRENCY_CD 
WHERE 
    STG.PROCESS_INSTANCE = 22195604 
    AND RT.EFFDT = (
        SELECT MAX(EFFDT) 
        FROM PS_CTS_IQN_BU_RATE RT1 
        WHERE RT.BUSINESS_UNIT = RT1.BUSINESS_UNIT 
          AND RT1.EFFDT <= SYSDATE
    ) 
    AND STG.STANDARD_RATE = 0 
    AND STG.STANDARD_HOURS = 0 
    AND STG.BUSINESS_UNIT IN ( 
        SELECT DISTINCT BUSINESS_UNIT 
        FROM PS_CTS_IQN_BU_RATE
    );

Additional Notes to Consider #

• Performance Optimization: The subquery for MAX(EFFDT) runs once per matched row. If you're working with a large dataset, precomputing these max effective dates in a CTE or temporary table can significantly speed up the query.
• Redundant Condition: The final AND STG.BUSINESS_UNIT IN (...) clause is technically redundant. Since the ON clause already joins STG.BUSINESS_UNIT = RT.BUSINESS_UNIT, any matched row will automatically have a BUSINESS_UNIT present in PS_CTS_IQN_BU_RATE. You can remove this to simplify the query unless you have a specific edge case to account for.

内容的提问来源于stack exchange，提问作者Muneendra