Hi,
I have 2 tables and am trying to perform the following but running into issues. I have all the ddls, dml below and need some help why it doesn't work if it's NULL or spaces comparison in the merge doesn't work and keeps inserting the data.
Here is the brief intro on what I am trying to do
1. If the row in
DTL_STG table matches with a row in
DETAILS table it does not insert but just update
DETAILS.IN_USE_FLAG to '*Y*'
2. If a row does not match it inserts it into
DETAILS table and also sets
DETAILS.IN_USE_FLAG to '*Y*'.
3. And finally for all the rows in
DETAILS that does not exist in
DTL_STG set
DETAILS.IN_USE_FLAG to '*N*'
Here are the Table Create Scripts and Sequence script
CREATE TABLE "DETAILS"
(
"ORFA_ID" NUMBER,
"ORG_ID" NUMBER(15,0),
"ORG_CODE" VARCHAR2(3 BYTE),
"ORG_NAME" VARCHAR2(240 BYTE),
"REGION_ID" NUMBER(15,0),
"REGION_NAME" VARCHAR2(240 BYTE),
"ASSET_SERIAL_NUMBER" VARCHAR2(30 BYTE),
"ASSET_NUMBER" VARCHAR2(30 BYTE),
"ASSET_DESCRIPTION" VARCHAR2(240 BYTE),
"ASSET_CATEGORY" VARCHAR2(163 BYTE),
"AREA_CODE" VARCHAR2(30 BYTE),
"AREA_DESCRIPTION" VARCHAR2(240 BYTE),
"PROCESS_TYPE" VARCHAR2(20 BYTE),
"IN_USE_FLAG" CHAR(1 BYTE),
"INSERT_DT" DATE,
"UPDATE_DT" DATE
)
CREATE TABLE "DTL_STG"
(
"INSTANCE_ID" VARCHAR2(250 BYTE),
"ORG_ID" NUMBER(15,0),
"ORG_CODE" VARCHAR2(3 BYTE),
"ORG_NAME" VARCHAR2(240 BYTE),
"REGION_ID" NUMBER(15,0),
"REGION_NAME" VARCHAR2(240 BYTE),
"ASSET_SERIAL_NUMBER" VARCHAR2(30 BYTE),
"ASSET_NUMBER" VARCHAR2(30 BYTE),
"ASSET_DESCRIPTION" VARCHAR2(240 BYTE),
"ASSET_CATEGORY" VARCHAR2(163 BYTE),
"AREA_CODE" VARCHAR2(30 BYTE),
"AREA_DESCRIPTION" VARCHAR2(240 BYTE),
"PROCESS_TYPE" VARCHAR2(20 BYTE)
)
CREATE SEQUENCE "ORFA_ID_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 41 CACHE 20 NOORDER NOCYCLE ;
Here are the 3 insert staements that insert data in DTL_STG table. Run these first
INSERT
INTO DTL_STG
(
ORG_ID,
ORG_CODE,
ORG_NAME,
REGION_ID,
REGION_NAME,
ASSET_SERIAL_NUMBER,
ASSET_NUMBER,
ASSET_DESCRIPTION,
ASSET_CATEGORY,
AREA_CODE,
AREA_DESCRIPTION,
PROCESS_TYPE
)
VALUES
(
88888,
NULL,
NULL,
10972,
'WESTERN OPER',
'4922AREA',
'INVER HEIGHTS TERM',
'IN GROVE HEI TERM',
'TERMIN',
'VERG',
NULL,
'ABC'
);
INSERT
INTO DTL_STG
(
ORG_ID,
ORG_CODE,
ORG_NAME,
REGION_ID,
REGION_NAME,
ASSET_SERIAL_NUMBER,
ASSET_NUMBER,
ASSET_DESCRIPTION,
ASSET_CATEGORY,
AREA_CODE,
AREA_DESCRIPTION,
PROCESS_TYPE
)
VALUES
(
88888,
'',
' ',
10972,
'WESTERN OPER',
'4922AREA',
'INVER HEIGHTS TERM',
'IN GROVE HEI TERM',
'TERMIN',
'VERG',
NULL,
' ass '
);
INSERT
INTO DTL_STG
(
ORG_ID,
ORG_CODE,
ORG_NAME,
REGION_ID,
REGION_NAME,
ASSET_SERIAL_NUMBER,
ASSET_NUMBER,
ASSET_DESCRIPTION,
ASSET_CATEGORY,
AREA_CODE,
AREA_DESCRIPTION,
PROCESS_TYPE
)
VALUES
(
99999,
NULL,
NULL,
19972,
'WES45TERN OPER',
'4922qAREA',
'INVEqR HEIGHTS TERM',
'IN GROVE HEqI TERM',
'TEqRMIN',
'TST',
NULL,
'EDF'
);
After running the above 3 inserts run the below can be part of Package or Procedure. It performs STEP 1, 2 and 3 as defined above.
MERGE
/*+ PARALLEL (P 4)*/
INTO DETAILS P USING DTL_STG T ON (NVL (P.ORG_ID, 0) = NVL (T.ORG_ID, 0) AND NVL (TRIM (P.ORG_CODE), '') = NVL (TRIM (T.ORG_CODE), '') AND NVL (TRIM (P.ORG_NAME), '') = NVL (TRIM (T.ORG_NAME), '') AND NVL (P.REGION_ID, 0) = NVL (T.REGION_ID, 0) AND NVL (TRIM (P.REGION_NAME), '') = NVL (TRIM (T.REGION_NAME), '') AND NVL (TRIM (P.ASSET_SERIAL_NUMBER), '') = NVL (TRIM (T.ASSET_SERIAL_NUMBER), '') AND NVL (TRIM (P.ASSET_NUMBER), '') = NVL (TRIM (T.ASSET_NUMBER), '') AND NVL (TRIM (P.ASSET_DESCRIPTION), '') = NVL (TRIM (T.ASSET_DESCRIPTION), '') AND NVL (TRIM (P.ASSET_CATEGORY), '') = NVL (TRIM (T.ASSET_CATEGORY), '') AND NVL (TRIM (P.AREA_CODE), '') = NVL (TRIM (T.AREA_CODE), '') AND NVL (TRIM (P.AREA_DESCRIPTION), '') = NVL (TRIM (T.AREA_DESCRIPTION), '') AND NVL (TRIM (P.PROCESS_TYPE), '') = NVL (TRIM (T.PROCESS_TYPE), ''))
WHEN MATCHED THEN
UPDATE SET P.IN_USE_FLAG = 'Y', P.UPDATE_DT = SYSDATE WHEN NOT MATCHED THEN
INSERT
(
ORFA_ID,
ORG_ID,
ORG_CODE,
ORG_NAME,
REGION_ID,
REGION_NAME,
ASSET_SERIAL_NUMBER,
ASSET_NUMBER,
ASSET_DESCRIPTION,
ASSET_CATEGORY,
AREA_CODE,
AREA_DESCRIPTION,
PROCESS_TYPE,
IN_USE_FLAG,
INSERT_DT
)
VALUES
(
ORFA_ID_SEQ.NEXTVAL,
NVL (T.ORG_ID, 0),
NVL (TRIM (T.ORG_CODE), ''),
NVL (TRIM (T.ORG_NAME), ''),
NVL (T.REGION_ID, 0),
NVL (TRIM (T.REGION_NAME), ''),
NVL (TRIM (T.ASSET_SERIAL_NUMBER), ''),
NVL (TRIM (T.ASSET_NUMBER), ''),
NVL (TRIM (T.ASSET_DESCRIPTION), ''),
NVL (TRIM (T.ASSET_CATEGORY), ''),
NVL (TRIM (T.AREA_CODE), ''),
NVL (TRIM (T.AREA_DESCRIPTION), ''),
NVL (TRIM (T.PROCESS_TYPE), ''),
'Y',
SYSDATE
);
UPDATE DETAILS X
SET IN_USE_FLAG = 'N',
UPDATE_DT = SYSDATE
WHERE ORFA_ID IN
(SELECT T.ORFA_ID
FROM
(SELECT NVL (ORG_ID, 0) ORG_ID,
NVL (TRIM (ORG_CODE), '') ORG_CODE,
NVL (TRIM (ORG_NAME), '') ORG_NAME,
NVL (REGION_ID, 0) REGION_ID,
NVL (TRIM (REGION_NAME), '') REGION_NAME,
NVL (TRIM (ASSET_SERIAL_NUMBER), '') ASSET_SERIAL_NUMBER,
NVL (TRIM (ASSET_NUMBER), '') ASSET_NUMBER,
NVL (TRIM (ASSET_DESCRIPTION), '') ASSET_DESCRIPTION,
NVL (TRIM (ASSET_CATEGORY), '') ASSET_CATEGORY,
NVL (TRIM (AREA_CODE), '') AREA_CODE,
NVL (TRIM (AREA_DESCRIPTION), '') AREA_DESCRIPTION,
NVL (TRIM (PROCESS_TYPE), '') PROCESS_TYPE
FROM DETAILS
MINUS
SELECT NVL (ORG_ID, 0) ORG_ID,
NVL (TRIM (ORG_CODE), '') ORG_CODE,
NVL (TRIM (ORG_NAME), '') ORG_NAME,
NVL (REGION_ID, 0) REGION_ID,
NVL (TRIM (REGION_NAME), '') REGION_NAME,
NVL (TRIM (ASSET_SERIAL_NUMBER), '') ASSET_SERIAL_NUMBER,
NVL (TRIM (ASSET_NUMBER), '') ASSET_NUMBER,
NVL (TRIM (ASSET_DESCRIPTION), '') ASSET_DESCRIPTION,
NVL (TRIM (ASSET_CATEGORY), '') ASSET_CATEGORY,
NVL (TRIM (AREA_CODE), '') AREA_CODE,
NVL (TRIM (AREA_DESCRIPTION), '') AREA_DESCRIPTION,
NVL (TRIM (PROCESS_TYPE), '') PROCESS_TYPE
FROM DTL_STG
) P,
DETAILS T
WHERE NVL (P.ORG_ID, 0) = NVL (T.ORG_ID, 0)
AND NVL (TRIM (P.ORG_CODE), '') = NVL (TRIM (T.ORG_CODE), '')
AND NVL (TRIM (P.ORG_NAME), '') = NVL (TRIM (T.ORG_NAME), '')
AND NVL (P.REGION_ID, 0) = NVL (T.REGION_ID, 0)
AND NVL (TRIM (P.REGION_NAME), '') = NVL (TRIM (T.REGION_NAME), '')
AND NVL (TRIM (P.ASSET_SERIAL_NUMBER), '') = NVL (TRIM (T.ASSET_SERIAL_NUMBER), '')
AND NVL (TRIM (P.ASSET_NUMBER), '') = NVL (TRIM (T.ASSET_NUMBER), '')
AND NVL (TRIM (P.ASSET_DESCRIPTION), '') = NVL (TRIM (T.ASSET_DESCRIPTION), '')
AND NVL (TRIM (P.ASSET_CATEGORY), '') = NVL (TRIM (T.ASSET_CATEGORY), '')
AND NVL (TRIM (P.AREA_CODE), '') = NVL (TRIM (T.AREA_CODE), '')
AND NVL (TRIM (P.AREA_DESCRIPTION), '') = NVL (TRIM (T.AREA_DESCRIPTION), '')
AND NVL (TRIM (P.PROCESS_TYPE), '') = NVL (TRIM (T.PROCESS_TYPE), '')
);
COMMIT;
Now run the above MERGE and UPDATE statements again and it still keeps inserting the rows with NULL or blank data basiaclly the 2 inserts above that conatins NULL and ' ' are the ones keep getting inserted and looks like MERGE is not able to handle the comparison of NULL or ' ' with spaces or blank. As I do have NVL, TRIM but seems like it's not working.
Any help is appreciated.
Thanks