【Oracle】ORA-00907の原因と解決方法｜missing right parenthesis・右括弧がありませんの直し方

-- NG: NVLの閉じ括弧が足りない
SELECT employee_id,
       NVL(commission_pct, 0 AS commission_pct
FROM employees;

-- OK: 関数呼び出しを閉じてから別名を付ける
SELECT employee_id,
       NVL(commission_pct, 0) AS commission_pct
FROM employees;

-- NG: CHECK制約の閉じ括弧が足りない
CREATE TABLE employees_work (
    employee_id NUMBER PRIMARY KEY,
    salary NUMBER(10, 2),
    status VARCHAR2(10),
    CONSTRAINT chk_salary CHECK (salary >= 0
);

-- OK: CHECK条件とCREATE TABLE全体の括弧をそれぞれ閉じる
CREATE TABLE employees_work (
    employee_id NUMBER PRIMARY KEY,
    salary NUMBER(10, 2),
    status VARCHAR2(10),
    CONSTRAINT chk_salary CHECK (salary >= 0)
);

-- NG: 列定義内でFOREIGN KEYを直接書いている
CREATE TABLE order_items (
    item_id NUMBER PRIMARY KEY,
    order_id NUMBER FOREIGN KEY REFERENCES orders(order_id),
    quantity NUMBER
);

-- OK: 列制約ならREFERENCESだけを書く
CREATE TABLE order_items (
    item_id NUMBER PRIMARY KEY,
    order_id NUMBER REFERENCES orders(order_id),
    quantity NUMBER
);

-- OK: 表制約ならFOREIGN KEY (列名) として書く
CREATE TABLE order_items (
    item_id NUMBER PRIMARY KEY,
    order_id NUMBER,
    quantity NUMBER,
    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- NG: NUMBERの閉じ括弧がない
CREATE TABLE sales_work (
    sales_id NUMBER PRIMARY KEY,
    amount NUMBER(12, 2,
    created_at DATE
);

-- OK
CREATE TABLE sales_work (
    sales_id NUMBER PRIMARY KEY,
    amount NUMBER(12, 2),
    created_at DATE
);

-- NG: INリストとCHECKの括弧が崩れている
CREATE TABLE orders_work (
    order_id NUMBER PRIMARY KEY,
    status VARCHAR2(20),
    CONSTRAINT chk_order_status CHECK (status IN ('NEW', 'PAID', 'CANCELLED')
);

-- OK
CREATE TABLE orders_work (
    order_id NUMBER PRIMARY KEY,
    status VARCHAR2(20),
    CONSTRAINT chk_order_status CHECK (status IN ('NEW', 'PAID', 'CANCELLED'))
);

-- NG: INリストを閉じていない
SELECT *
FROM employees
WHERE department_id IN (10, 20, 30;

-- OK
SELECT *
FROM employees
WHERE department_id IN (10, 20, 30);

-- NG: サブクエリの閉じ括弧が足りない
SELECT *
FROM employees e
WHERE e.department_id = (
    SELECT d.department_id
    FROM departments d
    WHERE d.department_name = 'Sales';

-- OK
SELECT *
FROM employees e
WHERE e.department_id = (
    SELECT d.department_id
    FROM departments d
    WHERE d.department_name = 'Sales'
);

-- NG: CASE式をENDで閉じていない
SELECT employee_id,
       NVL(CASE
             WHEN salary >= 500000 THEN bonus
             ELSE 0
           , 0) AS bonus_amount
FROM employees;

-- OK: CASEをENDで閉じてからNVLの引数を続ける
SELECT employee_id,
       NVL(CASE
             WHEN salary >= 500000 THEN bonus
             ELSE 0
           END, 0) AS bonus_amount
FROM employees;

-- NG: NVLの第2引数と閉じ括弧の位置が崩れている
SELECT NVL(TO_CHAR(hire_date, 'YYYY-MM-DD'), '未設定' AS hire_date_text
FROM employees;

-- OK: TO_CHARを閉じ、NVLも閉じる
SELECT NVL(TO_CHAR(hire_date, 'YYYY-MM-DD'), '未設定') AS hire_date_text
FROM employees;

-- NG: 条件追加の途中で閉じ括弧が欠けたSQLになっている
SELECT *
FROM employees
WHERE (department_id = :dept_id
  AND status = 'ACTIVE';

-- OK: 生成後のSQLで括弧が閉じているか確認する
SELECT *
FROM employees
WHERE (department_id = :dept_id
  AND status = 'ACTIVE');