Handle duplicate Configurations (merged) [message #631294] |
Mon, 12 January 2015 08:38 |
|
jg5111
Messages: 5 Registered: January 2015
|
Junior Member |
|
|
HI All,
Need to handle duplicate Configurations coming in below data.
No. Parameter STATUS
1 STO ACTIVE
2 STO ACTIVE
3 BOS ACTIVE
4 KYC INACTIVE
5 KYC INACTIVE
Currently, I am able to fetch duplicate record (Parameter) which is coming first in sequence in another table.
example - Parameter - STO with ACTIVE status
My task is to fetch data based upon STATUS i.e. If STATUS of same PARAMETER is ACTIVE in both then fetch first coming ACTIVE parameter.
IF one is INACTIVE, other is ACTIVE for PARAMETER with same name - select ACTIVE one.
IF both are INACTIVE for PARAMETER with same name - select first INACTIVE one.
Please help to write this procedure block.
[Updated on: Mon, 12 January 2015 08:43] Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: Handle duplicate Configurations [message #631302 is a reply to message #631301] |
Mon, 12 January 2015 09:13 |
|
jg5111
Messages: 5 Registered: January 2015
|
Junior Member |
|
|
Hi ALL,
I am getting duplicate data like.
No. Parameter STATUS
1 STO ACTIVE
2 STO ACTIVE
3 BOS ACTIVE
4 KYC INACTIVE
5 KYC INACTIVE
Task is to use this data and populate in another table.
Please see below procedure for populating data in another table.
PROCEDURE pr_PopulateConfigType(nXmlConfigurationId IN NUMBER,
vCnfgTypName IN VARCHAR,
vXmlNewPosTag IN VARCHAR,
vStatus IN VARCHAR,
vCnfgTypDescription IN VARCHAR,
vStrDbTyp IN VARCHAR,
nStgCnfgTypId OUT NUMBER,
nCnfgTypId OUT NUMBER,
nCnfgTypExists OUT NUMBER,
nProcessExecNum IN NUMBER) IS
nCount NUMBER;
nStatus NUMBER;
nStrDbTyp NUMBER;
nStus NUMBER;
BEGIN
/* SELECT COUNT(*)
INTO nCount
FROM strdb_cnfg_typ a,
stg_rfm2_strdb_cnfg_typ b
WHERE a.cnfg_typ_id = b.cnfg_typ_id
AND UPPER(TRIM(b.cnfg_typ_na)) = UPPER(TRIM(vStrDbTyp));*/
SELECT COUNT(*)
INTO nCount
FROM strdb_cnfg_typ
WHERE UPPER(TRIM(cnfg_typ_na)) = UPPER(TRIM(vStrDbTyp));
IF nCount = 1 THEN
nCnfgTypExists := 1;
-- Log Message stating already exists in RFM2 production
nTemp := pkg_Misc.fn_InsertLogDetail(nLogId, NULL, vCnfgTypName, 'STRDB_CNFG_TYP', NULL, NULL, 'Configuration type already exists in RFM2 Production table');
end if;
if ncount=0 then
SELECT COUNT(*)
INTO nCount
FROM stg_rfm2_strdb_cnfg_typ
WHERE UPPER(TRIM(cnfg_typ_na)) = UPPER(TRIM(vStrDbTyp));
IF nCount = 1 THEN
nCnfgTypExists := 1;
-- Log Message stating already exists in RFM2 staging
nTemp := pkg_Misc.fn_InsertLogDetail(nLogId, NULL, vCnfgTypName, 'STRDB_CNFG_TYP', NULL, NULL, 'Configuration type already exists in RFM2 staging table');
end if;
end if;
IF nCount = 0 THEN
nCnfgTypExists := 0;
nStgCnfgTypId := pkg_misc.fn_GetMigSeqNum('STG_CNFG_TYP_ID');
nCnfgTypId := pkg_Misc.fn_GetSeqNum('CNFG_TYP_ID');
If vStatus = 'ACTIVE' THEN
nStatus := 1;
ELSIF vStatus = 'INACTIVE' THEN
nStatus := 0;
END IF;
BEGIN
nStrDbTyp := Pkg_Misc.fn_GetLookupCode(pkg_misc.fn_GetControlValue('CONSTANT','CONFIGPARM','LEXID_STRDBTYPE'),vStrDbTyp);
IF nStrDbTyp IS NULL THEN
nStus :=fn_CreateLookupEntry(vStrDbTyp);
IF nStus =1 THEN
nStrDbTyp := Pkg_Misc.fn_GetLookupCode(pkg_misc.fn_GetControlValue('CONSTANT','CONFIGPARM','LEXID_STRDBTYPE'),vStrDbTyp
END IF;
END IF;
END;
INSERT INTO stg_rfm2_strdb_cnfg_typ
(stg_cnfg_typ_id,
xml_configuration_id,
cnfg_typ_id,
cnfg_typ_na,
xml_newpos_tag,
stus,
dspl_ord,
glb_flag,
cnfg_typ_ds,
strdb_typ,
process_exec_num)
VALUES
(nStgCnfgTypId,
nXmlConfigurationId,
nCnfgTypId,
vStrDbTyp,
vStrDbTyp,
nStatus,
NULL,
1,
vCnfgTypDescription,
nStrDbTyp,
nProcessExecNum);
END IF;
EXCEPTION
WHEN OTHERS THEN
nTemp := pkg_Misc.fn_InsertLogDetail(nLogId, NVL(vCnfgTypDescription,vStrDbTyp), vCnfgTypDescription, nCnfgTypId, TO_CHAR(SQLCODE)||' , '||'Error '||dbms_utility.format_error_backtrace, SUBSTR(SQLERRM,1,500), NULL);
-- ROLLBACK;
vLogStus :=1;
END pr_PopulateConfigType;
|
|
|
|
|
|
|
|
Re: Handle duplicate Configurations [message #631331 is a reply to message #631297] |
Mon, 12 January 2015 13:55 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
jg5111 wrote on Mon, 12 January 2015 08:46First Ball n basis of First Come First Serve.
And how do YOU determine which on that is?
Oracle doesn't keep up with that because oracle doesn't care.
If I issue an insert, then go to lunch, then you issue an insert for the same values and commit, then I come back from lunch and commit ... who was first?
If I insert AND commit, THEN you insert AND commit .. well, you and I may know mine was first, but oracle really doesn't give a flying fig. If you want to determine 'first', then YOU have to have something in the data itself to make that determination.
Of course, if both rows are identical across ALL columns, then it really doesn't matter which one, does it?
|
|
|
|
|
|
|
|
|
|
|