This thread explains Boolean algebra in some Rows B-)
*****************************************************************
1. What is Boolean algebra ? ?:|
http://en.wikipedia.org/wiki/Elementary_Boolean_algebra
*****************************************************************
2. What is usage of "Boolean algebra in some Rows" ? ?:|
It is very effective when we want to check wheather some Rows meet conditions or not.
**************************
For exsample1
create table boolEx1(ID,Val) as
select 1,'aaa' from dual union all
select 1,'bbb' from dual union all
select 1,'ccc' from dual union all
select 2,'aaa' from dual union all
select 2,'aaa' from dual union all
select 2,'ccc' from dual union all
select 3,'aaa' from dual union all
select 3,'bbb' from dual union all
select 4,'eee' from dual;
chk1 I will check each ID has nothing either 'aaa' or 'ccc'.
chk2 I will check each ID has at least 'aaa' or 'ccc'.
chk3 I will check each ID has both 'aaa' and 'ccc'.
chk4 I will check each ID has something other than 'aaa'
or 'ccc'.
chk5 I will check each ID has at least least 'aaa' or 'ccc'.
select ID,Val,
min(case when Val in('aaa','ccc') then 1 else 0 end)
over(partition by ID) as chk1,
max(case when Val in('aaa','ccc') then 1 else 0 end)
over(partition by ID) as chk2,
max(case when Val='aaa' then 1 else 0 end)
over(partition by ID)
*max(case when Val='ccc' then 1 else 0 end)
over(partition by ID)
as chk3,
min(case when Val in('aaa','ccc') then 0 else 1 end)
over(partition by ID) as chk4,
max(case when Val in('aaa','ccc') then 1 else 0 end)
over(partition by ID) as chk5
from boolEx1
order by ID,Val;
ID VAL CHK1 CHK2 CHK3 CHK4 CHK5
-- --- ---- ---- ---- ---- ----
1 aaa 0 1 1 0 1
1 bbb 0 1 1 0 1
1 ccc 0 1 1 0 1
2 aaa 1 1 1 0 1
2 aaa 1 1 1 0 1
2 ccc 1 1 1 0 1
3 aaa 0 1 0 0 1
3 bbb 0 1 0 0 1
4 eee 0 0 0 1 0