CODEWITHSUNDEEP
sqloracle-database
Vijay
Vijay

Reputation: 49

Error(4,125): PL/SQL: ORA-00933: SQL command not properly ended

Syntax error comes when I add this WHERE ods_workday_w.test_table.CUSTOMER_ID = 12clause to the view,

CREATE OR REPLACE PROCEDURE TEST_POC IS
BEGIN
    INSERT INTO ods_workday.test_table (CUSTOMER_ID, CUSTOMER_NAME, CITY)
    SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY FROM ods_workday_w.test_table WHERE ods_workday_w.test_table.CUSTOMER_ID = 12
    WHERE NOT EXISTS (SELECT * FROM ods_workday.test_table 
          WHERE ods_workday.test_table.CUSTOMER_ID = ods_workday_w.test_table.CUSTOMER_ID AND 
                ods_workday.test_table.CUSTOMER_NAME = ods_workday_w.test_table.CUSTOMER_NAME AND
                ods_workday.test_table.CITY = ods_workday_w.test_table.CITY AND ROWNUM = 1);
END TEST_POC

Error(3,5): PL/SQL: SQL Statement ignored Error(5,5): PL/SQL: ORA-00933: SQL command not properly ended

I want to select attributes from the ods_workday_w.test_table, when the ods_workday_w.test_table.CUSTOMER_ID = 12(numeric input passed to the stored procedure) and use the values of the selected attributes to populate the destination table.

Upvotes: 0

Views: 78

Answers (2)

William Robertson
William Robertson

Reputation: 16001

I don't know how you can read that code unformatted and with verbose references. I couldn't make any sense of it until I laid it out cleanly:

create or replace procedure test_poc is
begin
    insert into ods_workday.test_table
         ( customer_id
         , customer_name
         , city )
    select t1.customer_id
         , t1.customer_name
         , t1.city
    from   ods_workday_w.test_table t1
    where  t1.customer_id = 12
    and    not exists
           ( select 1
             from   ods_workday.test_table t2
             where  t2.customer_id = t1.customer_id
             and    t2.customer_name = t1.customer_name
             and    t2.city = t1.city );
end test_poc;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143113

You can't have 2 WHEREs; another one has to be e.g. AND (see the comment). Also, terminate the block with a semi-colon. Finally, consider using table aliases instead of full table names (to improve readability). And - as @a_horse_with_no_name commented - terminate the whole CREATE statement with a slash /.

CREATE OR REPLACE PROCEDURE TEST_POC
IS
BEGIN
   INSERT INTO ods_workday.test_table (CUSTOMER_ID, CUSTOMER_NAME, CITY)
      SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY
        FROM ods_workday_w.test_table
       WHERE     ods_workday_w.test_table.CUSTOMER_ID = 12
             AND NOT EXISTS                                             --> here
                    (SELECT *
                       FROM ods_workday.test_table
                      WHERE     ods_workday.test_table.CUSTOMER_ID =
                                   ods_workday_w.test_table.CUSTOMER_ID
                            AND ods_workday.test_table.CUSTOMER_NAME =
                                   ods_workday_w.test_table.CUSTOMER_NAME
                            AND ods_workday.test_table.CITY =
                                   ods_workday_w.test_table.CITY
                            AND ROWNUM = 1);
END TEST_POC;

Upvotes: 2

Related Questions