Getting ORA-00920: invalid relational operator For Merge Statement

Vemula Muni

Hi All,

I am getting the ORA-00920: invalid relational operator message foe the below query. Please help on this.

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(RT1.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

    );

mNem

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(RT1.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

    );

mNem

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(RT1.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

    );

Gaz in Oz

Get rid of the space on line 11 and retry.

Wrote file afiedt.buf

  1  MERGE INTO PS_CT_IQN_ACC_STG STG USING PS_CTS_IQN_BU_RATE RT ON (STG.BUSINESS_UNIT = RT.BUSINESS_UNIT)  2  WHEN MATCHED THEN  3    UPDATE  4    SET STG.STANDARD_RATE      = RT.STANDARD_RATE,  5      STG.STANDARD_HOURS      = RT.HOURS_PER_DAY,  6      STG.CURRENCY_CD          = RT.CURRENCY_CD  7    WHERE STG.PROCESS_INSTANCE = 22195604  8    AND RT.EFFDT              =  9      (SELECT MAX(EFFDT)10      FROM PS_CTS_IQN_BU_RATE RT111      WHERE RT.BUSIN ESS_UNIT = RT1.BUSINESS_UNIT12      AND RT1.EFFDT          <= sysdate13      )14    AND STG.STANDARD_RATE      = 015    AND STG.STANDARD_HOURS  = 016    AND STG.BUSINESS_UNIT IN17      ( SELECT DISTINCT BUSINESS_UNIT FROM PS_CTS_IQN_BU_RATE18*    )19  /    WHERE RT.BUSIN ESS_UNIT = RT1.BUSINESS_UNIT                  *ERROR at line 11:ORA-00920: invalid relational operatorSQL>