我在撰寫插入表的程序時遇到問題，該表具有帶有 group by in for 回圈的 select 陳述句。
這是看我的代碼：
begin
FOR R IN (WITH
bez_oib
AS
(SELECT a.EXT_ACCOUNT_ID,
a.ACCOUNT_ID,
c.NAME,
c.ATTRIBUTE_VALUE_1 AS oib3
FROM MD_ACCOUNTS a, MD_CONTACTS c
WHERE a.ACCOUNT_ID = c.REFERENCE_ID
AND c.REFERENCE_TYPE_ID = 2
AND c.CONTACT_TYPE_ID = 1
AND a.EXT_ACCOUNT_ID IN ( SELECT a.EXT_ACCOUNT_ID
FROM MD_ACCOUNTS a
GROUP BY a.EXT_ACCOUNT_ID
HAVING COUNT (DISTINCT a.account_id) > 1)
AND c.ATTRIBUTE_VALUE_1 IS NULL),
sa_oib
AS
(SELECT a.EXT_ACCOUNT_ID,
a.ACCOUNT_ID,
c.NAME,
c.ATTRIBUTE_VALUE_1 AS oib2
FROM MD_ACCOUNTS a, MD_CONTACTS c
WHERE a.ACCOUNT_ID = c.REFERENCE_ID
AND c.REFERENCE_TYPE_ID = 2
AND c.CONTACT_TYPE_ID = 1
AND a.EXT_ACCOUNT_ID IN ( SELECT a.EXT_ACCOUNT_ID
FROM MD_ACCOUNTS a
GROUP BY a.EXT_ACCOUNT_ID
HAVING COUNT (DISTINCT a.account_id) > 1)
AND c.ATTRIBUTE_VALUE_1 IS NOT NULL)
SELECT BEZ_OIB.EXT_ACCOUNT_ID as SIFRA_KK,
BEZ_OIB.ACCOUNT_ID as a,
BEZ_OIB.NAME as c ,
BEZ_OIB.oib3 as b ,
SUM (IH1.REST_TO_PAY) AS PREOSTALI_DUG_BEZ_OIB,
SA_OIB.EXT_ACCOUNT_ID as s,
SA_OIB.ACCOUNT_ID as d,
SA_OIB.NAME as e,
SA_OIB.oib2 as f,
SUM (IH2.REST_TO_PAY) AS PREOSTALI_DUG_SA_OIB
FROM bez_oib,
sa_oib,
CA_INVOICE_HEADERS IH1,
CA_INVOICE_HEADERS IH2
WHERE bez_oib.EXT_ACCOUNT_ID = sa_oib.EXT_ACCOUNT_ID
AND bez_oib.NAME <> sa_oib.name
AND BEZ_OIB.ACCOUNT_ID = IH1.ACCOUNT_ID( )
AND SA_OIB.ACCOUNT_ID = IH2.ACCOUNT_ID( )
AND BEZ_OIB.EXT_ACCOUNT_ID NOT IN ('30150151054',
'33671110205',
'31841000148',
'31840520300',
'30200150216',
'33670011076')
GROUP BY BEZ_OIB.EXT_ACCOUNT_ID,
BEZ_OIB.ACCOUNT_ID,
BEZ_OIB.NAME,
BEZ_OIB.oib3,
SA_OIB.EXT_ACCOUNT_ID,
SA_OIB.ACCOUNT_ID,
SA_OIB.NAME,
SA_OIB.oib2)
LOOP
insert into dun_exception (ID, TYPE, VALUE, ACTION, VALID_FROM, VALID_TO, STATE, NOTE, CREATED_BY, CREATE_DATE, MODIFIED_BY, MODIFY_DATE, PROC_NAME, BUYER_ID, REASON) values (DUN_EXCEPTION_SEQ.nextval,'BUYER_ID',r.sifra_kk,null,date'2021-10-26', date'2022-12-31',1,'Jedna ?K, razli?it naziv, ima OIB - nema OIB', 'sbelcic',sysdate,null,null,nul,null,null);
END LOOP;
END;
在我嘗試執行它后，我收到以下錯誤：
錯誤報告 - ORA-06550：第 66 行，第 351 列：PL/SQL：ORA-00984：此處不允許列
列特別是錯誤參考到 group by 陳述句的最后一行。
代碼應該將值插入表中，但它不起作用。
有人可以幫忙嗎？
uj5u.com熱心網友回復：
那是nul，斯捷潘。
<snip>
VALUES (DUN_EXCEPTION_SEQ.NEXTVAL,
'BUYER_ID',
r.sifra_kk,
NULL,
DATE '2021-10-26',
DATE '2022-12-31',
1,
'Jedna ?K, razli?it naziv, ima OIB - nema OIB',
'sbelcic',
SYSDATE,
NULL,
NULL,
nul, --> here
NULL,
NULL);
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/338205.html
標籤：sql 甲骨文 plsql oracle-sqldeveloper
上一篇：雙擊以管理員身份執行