add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SampleTable (ColA,ColB,ColC,Desired_ColD,Desired_ColE,Desired_ColF
) as
Select 'A1','B1','C1',64,42,86 from dual union all
Select 'A1','B1',Null,28,24,23 from dual union all
Select 'A1','B1','',13,90,36 from dual union all
Select 'A1','B1','',27,27,82 from dual union all
Select 'A1','B2','C1',50,72,98 from dual union all
Select 'A1','B2','C2',54,58,34 from dual union all
Select 'A1','B2','C3',94,57,69 from dual union all
Select 'A1','B3','C1',91,89,77 from dual union all
Select 'A1','B3','C1',89,16,67 from dual union all
Select 'A1','','C1',50,72,40 from dual union all
Select 'A1','B3','',36,29,53 from dual union all
Select 'A1','B3','C2',69,65,85 from dual union all
Select 'A2','B1','C1',66,87,98 from dual union all
Select 'A2','B2','C1',35,87,85 from dual union all
Select '','B2','C2',56,76,52 from dual union all
Select '','B2','C2',33,79,18 from dual union all
Select 'A2','B2','C2',12,31,42 from dual union all
Select 'A2','B2','C2',11,79,83 from dual union all
Select 'A2','','C3',22,27,12 from dual union all
Select 'A2','B2','C3',38,58,48 from dual union all
Select 'A2','','C3',14,78,50 from dual union all
Select 'A2','B2','',83,33,12 from dual union all
Select 'A2','B3','C2',15,91,15 from dual union all
Select '','B3','C2',10,80,34 from dual union all
Select 'A2','B3','C2',97,76,48 from dual union all
Select 'A3','B1','C1',44,70,53 from dual union all
Select 'A3','B1','C1',38,13,11 from dual union all
Select 'A3','B1','C3',39,47,16 from dual union all
Select 'A3','B2','C1',90,72,13 from dual union all
Select 'A3','B2','C2',59,46,11 from dual
;
30 rows affected
--The Impaler's answer: exists + decode
with Desired_Column_Combination as (
Select 'A1' ColA,'B2' ColB,'C2' ColC from dual union all
Select 'A1' ColA,'B1' ColB,'' ColC from dual union all --Should've 3 records
Select 'A2' ColA,'' ColB,'C3' ColC from dual union all --Should've 2 records
Select 'A3' ColA,'B1' ColB,'C1' ColC from dual
)
select *
from SampleTable S
where exists (
select 1 from Desired_Column_Combination d
where decode(s.ColA, d.ColA, 1, 0) = 1
and decode(s.ColB, d.ColB, 1, 0) = 1
and decode(s.ColC, d.ColC, 1, 0) = 1
);
COLA COLB COLC DESIRED_COLD DESIRED_COLE DESIRED_COLF
A1 B1 null 28 24 23
A1 B1 null 13 90 36
A1 B1 null 27 27 82
A1 B2 C2 54 58 34
A2 null C3 22 27 12
A2 null C3 14 78 50
A3 B1 C1 44 70 53
A3 B1 C1 38 13 11
--Google: exists + Intersect
with Desired_Column_Combination as (
Select 'A1' ColA,'B2' ColB,'C2' ColC from dual union all
Select 'A1' ColA,'B1' ColB,'' ColC from dual union all --Should've 3 records
Select 'A2' ColA,'' ColB,'C3' ColC from dual union all --Should've 2 records
Select 'A3' ColA,'B1' ColB,'C1' ColC from dual
)
select *
from SampleTable S
where exists (
select ColA,ColB,ColC from Desired_Column_Combination
Intersect
select ColA,ColB,ColC from dual
);
COLA COLB COLC DESIRED_COLD DESIRED_COLE DESIRED_COLF
A1 B1 null 28 24 23
A1 B1 null 13 90 36
A1 B1 null 27 27 82
A1 B2 C2 54 58 34
A2 null C3 22 27 12
A2 null C3 14 78 50
A3 B1 C1 44 70 53
A3 B1 C1 38 13 11
--My original syntax: Condition is carefully chosen
--so that no null value is involved and will give correct result
with Desired_Column_Combination as (
Select 'A1' ColA,'B2' ColB,'C2' ColC from dual union all
Select 'A1' ColA,'B1' ColB,'C3' ColC from dual union all --Should've 3 records
Select 'A2' ColA,'B2' ColB,'C3' ColC from dual union all --Should've 2 records
Select 'A3' ColA,'B1' ColB,'C1' ColC from dual
)
select *
from SampleTable S
where (ColA,ColB,ColC) in (
select ColA,ColB,ColC from Desired_Column_Combination
);
COLA COLB COLC DESIRED_COLD DESIRED_COLE DESIRED_COLF
A1 B2 C2 54 58 34
A2 B2 C3 38 58 48
A3 B1 C1 44 70 53
A3 B1 C1 38 13 11