英文:
Oracle Form showing error ORA-01422 more than one record
问题
IF :CUSTOMER_ID IS NOT NULL AND :BRANCH_ID IS NOT NULL THEN
DECLARE
X_NO NUMBER;
X_VOUCHER_ID NUMBER;
BEGIN
SELECT
GL_VOUCHER_ID
INTO X_VOUCHER_ID
FROM
SM_SALES_INVOICES SI,
AR_CUSTOMERS AC,
GL_VOUCHERS GV
WHERE
AC.CUSTOMER_ID = SI.CUSTOMER_ID
AND SI.GL_VOUCHER_ID = GV.VOUCHER_ID
AND SI.CUSTOMER_ID = :CUSTOMER_ID;
UPDATE GL_VOUCHERS
SET
BRANCH_ID = :NEW_BRANCH_ID
WHERE
VOUCHER_ID = X_VOUCHER_ID;
MESSAGE('Branch is changed successfully!');
MESSAGE('Branch is changed successfully!');
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
COMMIT;
ELSE
MESSAGE('Please Enter Both Fields!');
MESSAGE('Please Enter both Fields!');
END IF;
英文:
IF :CUSTOMER_ID IS NOT NULL AND :BRANCH_ID IS NOT NULL THEN
DECLARE
X_NO NUMBER;
X_VOUCHER_ID NUMBER;
BEGIN
SELECT
GL_VOUCHER_ID
INTO X_VOUCHER_ID
FROM
SM_SALES_INVOICES SI,
AR_CUSTOMERS AC,
GL_VOUCHERS GV
WHERE
AC.CUSTOMER_ID = SI.CUSTOMER_ID
AND SI.GL_VOUCHER_ID = GV.VOUCHER_ID
AND SI.CUSTOMER_ID = :CUSTOMER_ID;
UPDATE GL_VOUCHERS
SET
BRANCH_ID = :NEW_BRANCH_ID
WHERE
VOUCHER_ID = X_VOUCHER_ID;
MESSAGE('Branch is changed sucessfully!');
MESSAGE('Branch is changed sucessfully!');
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
COMMIT;
ELSE
MESSAGE('Please Enter Both Fields!');
MESSAGE('Please Enter both Fields!');
END IF;
答案1
得分: 2
代码中的问题是，您的第一个 SELECT 查询返回了多条记录，而您试图将多个结果存储到单个变量 X_VOUCHER_ID 中。
您需要按照以下方式更新表 GL_VOUCHERS，而不将 GL_VOUCHER_ID 存储到 X_VOUCHER_ID 中：
IF :CUSTOMER_ID IS NOT NULL AND :BRANCH_ID IS NOT NULL THEN
DECLARE
X_NO NUMBER;
BEGIN
UPDATE GL_VOUCHERS
SET
BRANCH_ID = :NEW_BRANCH_ID
WHERE
VOUCHER_ID IN (
SELECT
GL_VOUCHER_ID
FROM
SM_SALES_INVOICES SI,
AR_CUSTOMERS AC,
GL_VOUCHERS GV
WHERE
AC.CUSTOMER_ID = SI.CUSTOMER_ID
AND SI.GL_VOUCHER_ID = GV.VOUCHER_ID
AND SI.CUSTOMER_ID = :CUSTOMER_ID
);
MESSAGE('Branch is changed successfully!');
MESSAGE('Branch is changed successfully!');
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
COMMIT;
ELSE
MESSAGE('Please Enter Both Fields!');
MESSAGE('Please Enter both Fields!');
END IF;
以上代码将更新与内部子查询中的 GL_VOUCHER_ID 匹配的 GL_VOUCHERS 表的所有记录。
祝好！
英文:
The issue with your code is that your first SELECT query is returning multiple records and you are trying to store multiple results into single variable X_VOUCHER_ID.
You need to update the table GL_VOUCHERS without saving GL_VOUCHER_ID into X_VOUCHER_ID as following:
> IF :CUSTOMER_ID IS NOT NULL AND :BRANCH_ID IS NOT NULL THEN
> DECLARE
> X_NO NUMBER;
> -- removed following variable
> -- X_VOUCHER_ID NUMBER;
> BEGIN
> --REMOVED THIS SELECT CLAUSE
> -- SELECT
> -- GL_VOUCHER_ID
> -- INTO X_VOUCHER_ID
> -- FROM
> -- SM_SALES_INVOICES SI,
> -- AR_CUSTOMERS AC,
> -- GL_VOUCHERS GV
> -- WHERE
> -- AC.CUSTOMER_ID = SI.CUSTOMER_ID
> -- AND SI.GL_VOUCHER_ID = GV.VOUCHER_ID
> -- AND SI.CUSTOMER_ID = :CUSTOMER_ID;
> UPDATE GL_VOUCHERS
> SET
> BRANCH_ID = :NEW_BRANCH_ID
> WHERE
> VOUCHER_ID IN ( -- ADDED THIS IN CLAUSE
> SELECT
> GL_VOUCHER_ID
> FROM
> SM_SALES_INVOICES SI,
> AR_CUSTOMERS AC,
> GL_VOUCHERS GV
> WHERE
> AC.CUSTOMER_ID = SI.CUSTOMER_ID
> AND SI.GL_VOUCHER_ID = GV.VOUCHER_ID
> AND SI.CUSTOMER_ID = :CUSTOMER_ID
> );
>
> MESSAGE('Branch is changed sucessfully!');
> MESSAGE('Branch is changed sucessfully!');
> EXCEPTION
> WHEN NO_DATA_FOUND THEN
> NULL;
> END;
>
> COMMIT;
> ELSE
> MESSAGE('Please Enter Both Fields!');
> MESSAGE('Please Enter both Fields!');
> END IF;
The above code will update all the records of the GL_VOUCHERS table which matches the GL_VOUCHER_ID from an inner subquery.
Cheers!!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论