I am writing a query to pull data from various tables and am new to data analysis.
Here is the problem:
The address (addr1 and addr2 columns) from table B should be displayed as 'NULL' when the addr_cd is not 'R' in table A. The address (addr1 and addr2 columns) from table B should display values when the addr_cd is having both 'R' and non'R' values in table 'A'.
Currently I have written the below code:
with t1 as
(
select * from table1 a where a.cd = 'R' and exists
(select b.cd from table1 b where a.id = b.id and b.cd != 'R')
),
t2 as
(
select * from table1 a where a.cd != 'R' and not exists
(select b.cd from table1 b where a.id = b.id and b.cd = 'EX')
),
t as
(
SELECT DISTINCT some column names..,
c.addr1,
c.addr2,
left JOIN t1
ON c.id = t1.id
AND c.id2 = t1.id2
AND A.NBR = t1.NBR
left JOIN t2
ON A.id = t2.id
AND A.id2 = t2.id2
AND A.NBR = t2.NBR
) select distinct some columns.., id, addr1, addr2 from t;
Note- I am currently getting duplicate values (one row with NULL for non'R' cd and one row with values for 'R' cd). But expected output is to have only rows with values if cd is both 'R' and non'R' for a particular ID. If ID does not have 'R' value then we need to have rows with 'NULL' values.
This is how I understood it (with my own sample data, as you didn't provide any):
SQL> with
2 b (id, addr1, addr2) as
3 (select 1, 'Address 1-1', 'Address 1-2' from dual union all
4 select 2, 'Address 2-1', 'Address 2-2' from dual union all
5 select 3, 'Address 3-1', 'Address 3-2' from dual
6 ),
7 a (id, addr_cd) as
8 (select 1, 'X' from dual union all --> ID = 1 doesn't have R
9 select 2, 'R' from dual union all --> ID = 2 has one R and one non-R
10 select 2, 'Y' from dual
11 ),
Query begins here: calculate number of Rs and non-Rs:
12 temp as
13 (select id,
14 sum(case when addr_cd <> 'R' then 1 else 0 end) as cnt_not_r,
15 sum(case when addr_cd = 'R' then 1 else 0 end) cnt_r
16 from a
17 group by id
18 )
Finally, join temp with b on id and - according to result of case expression - return NULL or address values:
19 select b.id,
20 --
21 case when t.cnt_not_r * t.cnt_r > 0 then b.addr1
22 when t.cnt_not_r > 0 then NULL
23 end as addr1,
24 --
25 case when t.cnt_not_r * t.cnt_r > 0 then b.addr2
26 when t.cnt_not_r > 0 then NULL
27 end as addr2
28 from temp t join b on b.id = t.id
29 order by b.id;
ID ADDR1 ADDR2
------------ ----------- -----------
1
2 Address 2-1 Address 2-2
SQL>