CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-insert
James_B
James_B

Reputation: 165

PL/SQL check to see if an inputted value exists in a different table

Goal: Verify storeName exists in the storeLocation table & Verify invoiceNumber does not exist In the invoiceHistory table.

With the code I have bellow, I can add to the table using insert into, but when I use a where exists and not exists, I get the following errors:

ORA-06550: line 7, column 5:

PL/SQL: ORA-00933: SQL command not properly ended

ACCEPT storename PROMPT 'Enter StoreName: '

ACCEPT price PROMPT 'Enter Price: '

ACCEPT tax PROMPT 'Enter Tax : '

ACCEPT total PROMPT 'Enter the total: '

ACCEPT invoicenumber PROMPT 'Enter invoice number: '


BEGIN
    INSERT INTO order VALUES (
        '&storename ',
        '&price ',
        '&tax',
        '&total ',
        '&invoicenumber ')
WHERE
        EXISTS(
            SELECT * FROM storelocation
            where upper(storelocation.storename) = upper('&storename '))
            
        AND NOT EXISTS(
            SELECT * FROM invoiceHistory 
            where invoiceHistory.invoicenumber = '&invoicenumber ')
        );

    COMMIT;
  
END;

Why is this error occurring and how do I avoid it?

Upvotes: 0

Views: 519

Answers (1)

sticky bit
sticky bit

Reputation: 37472

INSERT ... VALUES ... cannot have a WHERE clause. But INSERT ... SELECT ... can.

INSERT INTO order
            SELECT '&storename ',
                   '&price ',
                   '&tax',
                   '&total ',
                   '&invoicenumber '
                   FROM dual
                   WHERE EXISTS (SELECT *
                                        FROM storelocation
                                        WHERE upper(storelocation.storename) = upper('&storename '))
            
                         AND NOT EXISTS (SELECT *
                                                FROM invoicehistory 
                                                WHERE invoicehistory.invoicenumber = '&invoicenumber ');

But you should make a habit of explicitly listing the targeted columns in an INSERT. That makes sure everything goes where it's supposed to go.

(You may also check if you really want/need that trailing space in the string literals.)

Upvotes: 1

Related Questions