Hi There,
I am seeking help on below SQL. I’m trying to append data from INTERFACE.ACCOUNT_ADDRESS to ABC.acct_add table as part of our daily delta data load process.
ADDRESS_KEY column (primary key) in ABC.acct_add table is varchar and should be alphanumeric sequential number (AD-KEY-1, AD-KEY-2, etc). I am trying to run below insert statement but getting unique constraint validation error as it is not able to increase the address_key to the next value.
Thank you in advance!
CREATE TABLE ABC.acct_add
(
ADDRESS_KEY VARCHAR2(50 CHAR) NOT NULL ENABLE,
TENANT_CD VARCHAR2(50 CHAR) NOT NULL ENABLE,
ADDRESS_LINE_1 VARCHAR2(255 CHAR),
CITY VARCHAR2(100 CHAR),
COUNTRY_CD VARCHAR2(50 CHAR),
CONSTRAINT PK_ADDRESS1 PRIMARY KEY (ADDRESS_KEY, TENANT_CD)
);
INSERT INTO ABC.acct_add
(
ADDRESS_KEY,
TENANT_CD,
ADDRESS_LINE_1,
CITY,
COUNTRY_CD
)
SELECT
(select 'AD-KEY-'||(1 + NVL (max(to_number(address_key)), 0)) from ABC.acct_add) as ADDRESS_KEY,
'N/A',
ADDRESS_LINE_1,
CITY,
COUNTRY_CD,
FROM INTERFACE.ACCOUNT_ADDRESS WHERE EFFECTIVE_DATE=to_date('20190620','yyyymmdd')-1;
Error report -
ORA-00001: unique constraint (PK_ADDRESS1) violated