Hi All,
Let us consider the below example in Oracle DB:
create table xx_t1 ( col1 number, col2 varchar2(40));
create table xx_map ( col1 number, attribute1 varchar2(2));
insert into xx_t1 values (2101, 'AA');
insert into xx_t1 values (2101, 'AA1');
insert into xx_t1 values (2207, 'BB');
insert into xx_t1 values (2207, 'BB1');
insert into xx_t1 values (2230, 'CC');
insert into xx_map values( 2101, 'Y');
insert into xx_map values( 2207, null);
So... I would like to retrieve those records from table xx_t1 which appear in xx_map and attribute1 is Y, but i would like also to retrieve those records from xx_t1 which are do not appear in xx_map ignoring all the records where attribute1 is NULL.
The result wanted can be obtain from below query but I would like to get rid of the UNION ALL as this example is just a simple representation of a big query with big tables involved:
select t.* from xx_t1 t , xx_map m
where t.col1 = m.col1
and m.attribute1 ='Y'
union all
select * from xx_t1 t
where 1 = 1
and not exists
(select 1 from xx_map m
where m.col1 = t.col1
and nvl(attribute1,'Y') = 'Y')
Result:
COL1 COL2
---------- ----------------------------------------
2101 AA
2101 AA1
2230 CC
Oracle version : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Thanks in advance,
Aleks