Hi,
I have the below table data in my table
SQL> select * from mate2;
A_CD S_ID E_CD S_E S_NE E_IND
---------- ---------- ---------- ----- ----- -
200 1 2 A J Y
200 2 3 B A N
200 3 1 C D
200 4 2 A C Y
200 5 1 D C
200 6 3 B N Y
200 7 3 N K
I need to get count of distinct s_id group by a_cd and (S_E, S_NE) as letter codes with below conditions
1) take the S_NE when E_CD = 2 or (E_CD = 3 AND E_IND = N)
2) take the S_E when E_CD = 1 or (E_CD = 3 AND E_IND = Y)
3) take BOTH the S_E and S_NE when E_CD = 3 and E_IND is null
The output should be like
200 A 3
200 B 2
200 c 2
..
...
I have written something like this but its not working
select a.a_cd, a.letter_code, COUNT (DISTINCT a.s_id) AS cnt
FROM (SELECT m.a_cd,
m.s_id,
CASE
WHEN (m.e_cd = 1)
OR (m.e_cd = 3 AND m.e_ind = 'Y')
THEN
m.S_E
WHEN (m.e_cd = 2) OR (m.e_cd = 3 AND m.e_ind = 'N') then m.S_NE
WHEN m.e_ind is null then m.S_NE
END
AS letter_code from mate2 m
union
SELECT m.a_cd,
m.s_id,
CASE
WHEN (m.e_cd = 1)
OR (m.e_cd = 3 AND m.e_ind = 'Y')
THEN
m.S_E
WHEN (m.e_cd = 2) OR (m.e_cd = 3 AND m.e_ind = 'N') then m.S_NE
WHEN m.e_ind is null then m.S_NE
END
AS letter_code from mate2 m) a group by a.ai_cd,a.letter_code
Below is the table creation script and data that you can test it
create table Mate2(a_cd number(5),S_ID NUMBER, E_CD number(10), S_E varchar2(5), S_NE varchar2(5), E_IND CHAR(1 byte));
insert into Mate2 values (200,1,2,'A','J','Y');
insert into Mate2 values (200,2,3,'B','A','N');
insert into Mate2(a_cd,S_ID,E_CD,S_E,S_NE) values (200,3,1,'C','D');
insert into Mate2 values (200,4,2,'A','C','Y');
insert into Mate2(a_cd,S_ID,E_CD,S_E,S_NE) values (200,5,1,'D','C');
insert into Mate2 values (200,6,3,'B','N','Y');
insert into Mate2(a_cd,S_ID,E_CD,S_E,S_NE) values (200,7,3,'N','K');