OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (Oracle 12.1.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query Help [message #661244]

Sun, 12 March 2017 00:49

SADASIVAM
Messages: 16
Registered: October 2016

Junior Member

Hi All,
I need query help for below below requirement.

create table test1(ID1 Number,ID2 Number,Code Varchar2(30),Component Number,Priority Number );
 
Insert Into test1 Values(1,1,'0001',1,1);
Insert Into test1 Values(1,1,'0002',2,2);
Insert Into test1 Values(1,1,'0003',3,3);
 
Insert Into test1 Values(2,2,'0001',1,1);
Insert Into test1 Values(2,2,'0001',2,1);
 
Insert Into test1 Values(3,3,'0002',1,1);
Insert Into test1 Values(3,3,'0002',2,2);
 
Insert Into test1 Values(4,4,'0001',1,1);
Insert Into test1 Values(4,4,'0003',2,2);
 
Insert Into test1 Values(5,5,'0002',1,1);
Insert Into test1 Values(5,5,'0003',2,2);
 
Insert Into test1 Values(6,6,'0001',1,1);
Insert Into test1 Values(6,6,'0002',2,1);
 
Insert Into test1 Values(7,7,'0001',1,1);
Insert Into test1 Values(7,7,'0004',2,1);

I need to get the result for ID1 ,ID2 which having more than one record count with combination of ID1,ID2,Code

Select ID1,ID2 From test1 group by ID1,ID2,Code having count(*) > 1

Here ID 2 and 3 are matching.
Along with that I have one special scenario With Code '0001','0002','0003'.If at least one '0003' record available for ID1,ID2 combination then ,we need to decode the other records with the code 0001 and 0002 to 0003 within the ID1,ID2 combination for considering the count .In my data Id1 should be consider as count3 and return the ID.Same way for ID 4 and 5 consider the count as 2 and return the ID .This Special rule only for Code 0001,0002,0003 as fixed and any other code are consider individually(Normal Group by Having function).

So the query should return ID 1,2,3,4,5 Also.For that I got the below query ,

select id1, id2,
Listagg(component,',') Within Group (Order By Priority,component Asc) As Group_Of_Comp
    from
    (
    Select ID1,ID2,
    CODE,
    max(decode(CODE,'0003','0003')) over (partition by id1, id2) c1,
    component,priority
    From test1
    )
    group by id1, id2, case when CODE in ('0001','0002') and c1 = '0003' then c1 else code end
having count(*) > 1;

With this additionally i have to consider priority field to consider group of components.This makes complex.Please refer below example and let me know if you need more output.

If the priority ID is completely unique as below then same earlier logic apply ,0003 with 0001 or 0002 are same group.one row should come with Group_Of_Comp=1,2,3,4

Insert Into test1 Values(8,8,'0001',1,1);
Insert Into test1 Values(8,8,'0002',2,2);
Insert Into test1 Values(8,8,'0003',3,3);
Insert Into test1 Values(8,8,'0003',4,4);

For below id also one row should come with Group_Of_Comp=1,2,3 same as from above query.

Insert Into test1 Values(80,80,'0001',1,1);
Insert Into test1 Values(80,80,'0002',2,2);
Insert Into test1 Values(80,80,'0003',3,3);
Insert Into test1 Values(80,80,'0004',4,4);

But In case of same priority ID for more than one component,we have to consider as same group only when any one of the combination 0003+0001,or 0003+0002 only available.If 0003 along with both 0001 and 0002 is there then different group.So for ID 9 nuthing should retrun with count >1
[code]
Insert Into test1 Values(9,9,'0001',1,1);
Insert Into test1 Values(9,9,'0002',2,1);
Insert Into test1 Values(9,9,'0003',3,1);

Below case one row should come with Group_Of_Comp=1,2 as only 0003 with 0001 is there.

Insert Into test1 Values(10,10,'0001',1,1);
Insert Into test1 Values(10,10,'0003',2,1);

Below case one row should come with Group_Of_Comp=1,2 as only 0003 with 0002 is there.

Insert Into test1 Values(13,13,'0002',1,1);
Insert Into test1 Values(13,13,'0003',2,1);

Below case two row should come one with Group_Of_Comp=1,2 another one with Group_Of_Comp=3,4.

Insert Into test1 Values(11,11,'0001',1,1);
Insert Into test1 Values(11,11,'0003',2,1);
Insert Into test1 Values(11,11,'0002',3,2);
Insert Into test1 Values(11,11,'0003',4,2);

Below case only one row should come one with Group_Of_Comp=1,2 as comp 3 and 4 with different priority but code is not same and 0003 combination is not there.

Insert Into test1 Values(12,12,'0001',1,1);
Insert Into test1 Values(12,12,'0003',2,1);
Insert Into test1 Values(12,12,'0002',3,2);
Insert Into test1 Values(12,12,'0004',4,2);

Below case only four row should come one with Group_Of_Comp=1,2 , Group_Of_Comp=3,4,Group_Of_Comp=5,6,Group_Of_Comp=7,8

Insert Into test1 Values(14,14,'0001',1,1);
Insert Into test1 Values(14,14,'0001',2,1);
Insert Into test1 Values(14,14,'0001',3,2);
Insert Into test1 Values(14,14,'0001',4,2);
Insert Into test1 Values(14,14,'0001',5,3);
Insert Into test1 Values(14,14,'0001',6,3);
Insert Into test1 Values(14,14,'0001',7,4);
Insert Into test1 Values(14,14,'0001',8,4);

So basically user should give value for achieve the above logic(unique priority or pairs) either completely all priority unique or with set of pairs(more than one).If we give some priority with pair and few are single priority like below then only pair will be consider for group(either same code or 0003 logic),individual priority code not applicable with 0001+0003 or 0002+ 0003 logic.So expected result for below is two row one with Group_Of_Comp=1,2,3 another with Group_Of_Comp=4,5(remaining not >1)

Insert Into test1 Values(15,15,'0001',1,1);
Insert Into test1 Values(15,15,'0003',2,1);
Insert Into test1 Values(15,15,'0001',3,1);
Insert Into test1 Values(15,15,'0001',4,2);
Insert Into test1 Values(15,15,'0002',5,2);
Insert Into test1 Values(15,15,'0001',6,3);
Insert Into test1 Values(15,15,'0003',7,3);

Can you please help me on this.

Thanks in advance

Report message to a moderator

Previous Topic:	UTL_FILE.Put_Line carriage return.
Next Topic:	Help me with error Compilation unit analysis terminated (2 threads merged by bb)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 12:50:26 CDT 2024