Thanks for the question, Madhu.
Asked: April 03, 2024 - 8:26 am UTC
Last updated: May 17, 2024 - 4:39 am UTC
Version: 11g
Viewed 1000+ times
You Asked
From the following table , I want to build a query using analytical functions to retrieve the rows where there is a common document type for a Customer ID but not the mix. The output should be only ROW_NO 3,4,5 & 9,10
CREATE TABLE TAB_DOC_TYPES
(ROW_NO NUMBER,
CID NUMBER,
DOC_TYPE VARCHAR2(5)
);
INSERT INTO TAB_DOC_TYPES VALUES(1,101,'D1');
INSERT INTO TAB_DOC_TYPES VALUES(2,101,'DZ');
INSERT INTO TAB_DOC_TYPES VALUES(3,102,'DZ');
INSERT INTO TAB_DOC_TYPES VALUES(4,102,'DZ');
INSERT INTO TAB_DOC_TYPES VALUES(5,102,'DZ');
INSERT INTO TAB_DOC_TYPES VALUES(6,103,'D1');
INSERT INTO TAB_DOC_TYPES VALUES(7,103,'DZ');
INSERT INTO TAB_DOC_TYPES VALUES(8,103,'DZ');
INSERT INTO TAB_DOC_TYPES VALUES(9,104,'DZ');
INSERT INTO TAB_DOC_TYPES VALUES(10,104,'DZ');
ROW_NO CID DOC_TYPE
1 101 D1
2 101 DZ
3 102 DZ
4 102 DZ
5 102 DZ
6 103 D1
7 103 DZ
8 103 DZ
9 104 DZ
10 104 DZ
Here CID 101 & 103 has both D1 and DZ and query output shouldn't bring these records.
CID 102 & 104 has only one document type DZ and the query output should be bringing these record only.
and Connor said...
Can you elaborate on what you mean by
"retrieve the rows where there is a common document type for a Customer ID but not the mix"
===============
SQL> select * from TAB_DOC_TYPES;
ROW_NO CID DOC_T
---------- ---------- -----
1 101 D1
2 101 DZ
3 102 DZ
4 102 DZ
5 102 DZ
6 103 D1
7 103 DZ
8 103 DZ
9 104 DZ
10 104 DZ
10 rows selected.
SQL> select t.* , count(distinct doc_type) over ( partition by cid) as num_types
2 from TAB_DOC_TYPES t;
ROW_NO CID DOC_T NUM_TYPES
---------- ---------- ----- ----------
1 101 D1 2
2 101 DZ 2
4 102 DZ 1
3 102 DZ 1
5 102 DZ 1
6 103 D1 2
7 103 DZ 2
8 103 DZ 2
9 104 DZ 1
10 104 DZ 1
10 rows selected.
SQL> select * from
2 (
3 select t.* , count(distinct doc_type) over ( partition by cid) as num_types
4 from TAB_DOC_TYPES t
5 )
6 where num_types = 1;
ROW_NO CID DOC_T NUM_TYPES
---------- ---------- ----- ----------
4 102 DZ 1
3 102 DZ 1
5 102 DZ 1
9 104 DZ 1
10 104 DZ 1
SQL>