CODEWITHSUNDEEP
sqloracle-databaseoracle10goracle-apex
antonpug
antonpug

Reputation: 14286

SQL parsing fails, not sure what the issue is?

This parses fine:

MERGE INTO SECURE_GROUPS USING
    (SELECT :P94_NAME name FROM dual) data
ON (data.name = PEOPLE.PERSON_NAME)
WHEN NOT MATCHED THEN
    INSERT (PERSON_NAME) VALUES (data.name);

This fails:

 MERGE INTO SECURE_GROUPS USING
        (SELECT :P94_NAME name, :P94_NUMBER num FROM dual) data
    ON (data.name = PEOPLE.PERSON_NAME)
    WHEN NOT MATCHED THEN
        INSERT (PERSON_NAME, PERSON_NUMBER) VALUES (data.name, data.num);

The error message is:

1 error has occurred ORA-06550: line 10, column 51: PL/SQL: ORA-00923: FROM keyword not found where expected ORA-06550: line 9, column 1: PL/SQL: SQL Statement ignored

Upvotes: 2

Views: 2329

Answers (1)

aquinas
aquinas

Reputation: 23786

I think the issue is with using PEOPLE.PERSON_NAME like you are, without a select. Try doing it like this:

MERGE INTO SECURE_GROUPS
    USING (
        select :P94_NAME name, :P94_NUMBER num from dual
    ) data
    ON (data.name in (select PERSON_NAME from PEOPLE))
    WHEN NOT MATCHED THEN
        INSERT (PERSON_NAME, PERSON_NUMBER) VALUES (data.name, data.num)

Upvotes: 2

Related Questions