This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Getting ORA-00920: invalid relational operator For Merge Statement

Vemula Muni
Vemula Muni Member Posts: 57
edited Sep 20, 2019 2:51AM in SQL & PL/SQL

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

    );

Tagged:

Best Answer

  • mNem
    mNem Perpetual Learner -Member Posts: 1,380 Gold Trophy
    edited Sep 9, 2019 7:40AM Answer ✓

    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

        );

Answers

  • mNem
    mNem Perpetual Learner -Member Posts: 1,380 Gold Trophy
    edited Sep 9, 2019 7:40AM Answer ✓

    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
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Sep 9, 2019 7:41AM

    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>                                              
    Vemula Muni