Home » SQL & PL/SQL » SQL & PL/SQL » check all the distinct value match (oracle 10g windows)
check all the distinct value match [message #687728] |
Wed, 17 May 2023 11:22 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/e1807e23242de2805a657f4ad9ae6654?s=64&d=mm&r=g) |
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
hello Guru,
I have a scenario where i have call type incoming and outgoing against call_number
How can i check if the call_number has done both incoming and Outgoing, i have to filter out any phone number who has not done both the incoming and outgoing
below are the details
create table call_details (
call_type varchar(10),
call_number varchar(12),
call_duration int
);
insert into call_details values ('OUT','181868',13),
insert into call_details values('OUT','2159010',8),
insert into call_details values ('OUT','2159010',178),
insert into call_details values ('SMS','4153810',1),
insert into call_details values ('OUT','2159010',152),
insert into call_details values ('OUT','9140152',18),
insert into call_details values ('SMS','4162672',1),
insert into call_details values ('SMS','9168204',1),
insert into call_details values ('OUT','9168204',576),
insert into call_details values ('INC','2159010',5),
insert into call_details values ('INC','2159010',4),
insert into call_details values ('SMS','2159010',1),
insert into call_details values('SMS','4535614',1),
insert into call_details values ('OUT','181868',20),
insert into call_details values ('INC','181868',54),
insert into call_details values('INC','218748',20),
insert into call_details values('INC','2159010',9),
insert into call_details values ('INC','197432',66),
insert into call_details values('SMS','2159010',1),
insert into call_details values('SMS','4535614',1)
so from the above example call_number 218748 has only inc so can be ignored and call_number 9140152 has done outgoing only so can be ignored.
while call_number 181868 has both inc and out so it is valid record
|
|
|
|
|
|
|
|
|
Re: check all the distinct value match [message #687738 is a reply to message #687735] |
Thu, 18 May 2023 01:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Two more solutions:
select distinct(call_number) from call_details where call_type='OUT' and call_number in (select call_number from call_details where call_type='INC');
select distinct(call_number) from call_details a where call_type='OUT' and exists (select 1 from call_details b where a.call_number=b.call_number and b.call_type='INC'); Again, you would need to investigate the efficiency of the exec plans, as BB did.
|
|
|
Re: check all the distinct value match [message #687740 is a reply to message #687738] |
Thu, 18 May 2023 07:41 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Match recognize solution assuming there are no call types between INC and OUT if we order by call type like it is in the sample: INC, OUT, SMS. In addition, I assume call_number is not null:
select *
from call_details
match_recognize(
partition by call_number
order by call_type
pattern(i o)
define i as call_type = 'INC',
o as call_type = 'OUT'
)
/
CALL_NUMBER
------------
181868
2159010
Execution Plan
----------------------------------------------------------
Plan hash value: 667559662
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 160 | 1 (0)| 00:00:01 |
| 1 | VIEW | | 20 | 160 | 1 (0)| 00:00:01 |
| 2 | MATCH RECOGNIZE BUFFER DETERMINISTIC FINITE AUTOMATON| | 20 | 300 | 1 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | NUM_TYP_IDX | 20 | 300 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
616 bytes sent via SQL*Net to client
646 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
And if there can be call types between INC and OUT:
select *
from call_details
match_recognize(
partition by call_number
order by call_type
pattern(i e* o)
define i as call_type = 'INC',
e as call_type not in ('INC','OUT')
o as call_type = 'OUT'
)
/
CALL_NUMBER
------------
181868
2159010
Execution Plan
----------------------------------------------------------
Plan hash value: 1883887088
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 160 | 1 (0)| 00:00:01 |
| 1 | VIEW | | 20 | 160 | 1 (0)| 00:00:01 |
| 2 | MATCH RECOGNIZE BUFFER| | 20 | 300 | 1 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | NUM_TYP_IDX | 20 | 300 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
616 bytes sent via SQL*Net to client
711 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SY.
[Updated on: Thu, 18 May 2023 07:43] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:15:00 CDT 2024
|