Home » SQL & PL/SQL » SQL & PL/SQL » Handle duplicate Configurations (merged)
Handle duplicate Configurations (merged) [message #631294] Mon, 12 January 2015 08:38 Go to next message
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 #631295 is a reply to message #631294] Mon, 12 January 2015 08:42 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
When you read the guide, please pay particular attention to the section regarding providing a TEST CASE, because that is what you need to do.

In the meantime, would not a GROUP BY on PARAMETER,STATUS with MIN(NO) answer at least part of your requirement?
Re: Handle duplicate Configurations [message #631296 is a reply to message #631294] Mon, 12 January 2015 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

rows in a table are like balls in a basket.
If two balls with a single big X on each of them, which ball is the "first" ball?

>Please help to write this procedure block.
Please note that we don't do homework assignments.


Re: Handle duplicate Configurations [message #631297 is a reply to message #631296] Mon, 12 January 2015 08:46 Go to previous messageGo to next message
jg5111
Messages: 5
Registered: January 2015
Junior Member
First Ball n basis of First Come First Serve.

Re: Handle duplicate Configurations [message #631299 is a reply to message #631297] Mon, 12 January 2015 08:57 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
As already told to you, please show a test case. It is still not clear what do you expect as an output.
Re: Handle duplicate Configurations [message #631300 is a reply to message #631297] Mon, 12 January 2015 08:58 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
That doesn't change Blackswans point. The data needs to be ordered by a column(s) to get consistent results. In your case I assume you want to order by No.
Re: Handle duplicate Configurations [message #631301 is a reply to message #631294] Mon, 12 January 2015 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition to previous posts:
If you post a working Test case: create table and insert statements along with the result you want with these data then we will work with your table and data.

Post a test case that with several different cases.

Re: Handle duplicate Configurations [message #631302 is a reply to message #631301] Mon, 12 January 2015 09:13 Go to previous messageGo to next message
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 #631303 is a reply to message #631302] Mon, 12 January 2015 09:29 Go to previous messageGo to next message
John Watson
Messages: 8919
Registered: January 2010
Location: Global Village
Senior Member
Seems as though OP has not bothered to read my post at all. I shall de-subscribe from this.
Re: Handle duplicate Configurations [message #631304 is a reply to message #631303] Mon, 12 January 2015 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
NEVER do in PL/SQL, that which can be done in plain SQL; because SQL is MUCH more efficient & less resource intense.
Re: Handle duplicate Configurations [message #631306 is a reply to message #631304] Mon, 12 January 2015 09:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@OP, why UPPER and TRIM functions on both the column and the value? Seems unnecessary and design issue.

EDIT : If you expect values only in uppercase, then make sure the data in the respective column us stored in UPPER case.

[Updated on: Mon, 12 January 2015 09:42]

Report message to a moderator

Re: Handle duplicate Configurations [message #631307 is a reply to message #631306] Mon, 12 January 2015 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
INSERT single row per invocation of this procedure appears to be HIGHLY inefficient.
Re: Handle duplicate Configurations [message #631309 is a reply to message #631303] Mon, 12 January 2015 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
John Watson wrote on Mon, 12 January 2015 16:29
Seems as though OP has not bothered to read my post at all. I shall de-subscribe from this.


+1

Re: Handle duplicate Configurations [message #631331 is a reply to message #631297] Mon, 12 January 2015 13:55 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
jg5111 wrote on Mon, 12 January 2015 08:46
First 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?
Handling Duplicate Records [message #631381 is a reply to message #631294] Tue, 13 January 2015 05:16 Go to previous messageGo to next message
jg5111
Messages: 5
Registered: January 2015
Junior Member
HI,
I have a table with records like below
No. Parameter   STATUS
1   STO         INACTIVE
2   STO         ACTIVE
3   BOS         ACTIVE
4   KYC         INACTIVE
5   KYC         INACTIVE
6   ACC         ACTIVE
7   ACC         ACTIVE


Now result I am interested in is as follows

No. Parameter   STATUS
2   STO         ACTIVE
3   BOS         ACTIVE
4   KYC         INACTIVE
6   ACC         ACTIVE


That is, I want to select data on basis of STATUS .

1.Condition -- If STATUS is ACTIVE for both case of same Parameter - select first coming ACTIVE


2.Condition -- If STATUS is INACTIVE for both case of same Parameter - select first coming INACTIVE


3.Condition -- If STATUS is ACTIVE & INACTIVE for same Parameter - select ACTIVE


Please help for same query which I can use to write in my Procedure.
Re: Handling Duplicate Records [message #631382 is a reply to message #631381] Tue, 13 January 2015 05:26 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
SELECT *
FROM
  (SELECT t.*,
    row_number() OVER(PARTITION BY parameter ORDER BY status) rn
  FROM tab t
  )
WHERE rn = 1
ORDER BY NO;



Regards,
Lalit
Re: Handling Duplicate Records [message #631386 is a reply to message #631382] Tue, 13 January 2015 05:51 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@jg5111, why did you create a duplicate topic?
Re: Handling Duplicate Records [message #631387 is a reply to message #631386] Tue, 13 January 2015 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... why did you create a duplicate topic WITHOUT providing what has been asked?
(maybe this answer your question Lalit.Smile

Re: Handling Duplicate Records [message #631388 is a reply to message #631387] Tue, 13 January 2015 06:24 Go to previous messageGo to next message
jg5111
Messages: 5
Registered: January 2015
Junior Member
@Lalit..
I used your code..
select name, description, STATUS
from (select t.*,
             row_number() over (partition by parameter
                                order by status) seqnum
      from table Stg_Xml_Cpdb_Section t
     )
where seqnum = 1;


But it's throwing ORA-00906: missing left parenthesis
Re: Handling Duplicate Records [message #631389 is a reply to message #631388] Tue, 13 January 2015 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So fix the syntax by yourself or help us to help you providing the test case.

Re: Handling Duplicate Records [message #631391 is a reply to message #631389] Tue, 13 January 2015 06:45 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
from table 

You appear to have added an invalid keyword
Re: Handling Duplicate Records [message #631508 is a reply to message #631391] Wed, 14 January 2015 05:14 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
For reference, http://stackoverflow.com/questions/27941353/handle-duplicate-record-in-sql-pl-sql/27941553#27941553
Previous Topic: rownum query
Next Topic: SQL query for a project scheduler
Goto Forum:
  


Current Time: Tue Mar 19 03:27:28 CDT 2024