5 Replies Latest reply: Dec 28, 2009 5:10 AM by Aketi Jyuuzou RSS

    Boolean algebra in some Rows tutorial by Aketi Jyuuzou

    Aketi Jyuuzou
      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
        • 1. Re: Boolean algebra in some Rows tutorial by Aketi Jyuuzou
          Aketi Jyuuzou
          **************************
          For exsample2
          Of course.We can use above logic in select with group by and having
          select ID,
          min(case when Val in('aaa','ccc') then 1 else 0 end)
          as chk1,
          max(case when Val in('aaa','ccc') then 1 else 0 end)
          as chk2,
           max(case when Val='aaa' then 1 else 0 end)
          *max(case when Val='ccc' then 1 else 0 end)
          as chk3,
          min(case when Val in('aaa','ccc') then 0 else 1 end)
          as chk4,
          max(case when Val in('aaa','ccc') then 1 else 0 end)
          as chk5
            from boolEx1
          group by ID
          order by ID;
          
          ID  CHK1  CHK2  CHK3  CHK4  CHK5
          --  ----  ----  ----  ----  ----
           1     0     1     1     0     1
           2     1     1     1     0     1
           3     0     1     0     0     1
           4     0     0     0     1     0
          *****************************************************************
          3. What is summary of "Boolean algebra in some Rows" ? ?:|

          I have used this Logic.
          http://en.wikipedia.org/wiki/First-order_logic
          "max(case when P(X) then 1 else 0 end) = 1"
           is for some X:P(X)
          "min(case when P(X) then 1 else 0 end) = 1"
           is for all  X:P(X)
          "max(case when P(X) then 0 else 1 end) = 1"
           is for some X:not(P(X))
          "min(case when P(X) then 0 else 1 end) = 1"
           is for all  X:not(P(X))
          and sometimes I use below one.
           "max(case when P(X) then 1 else 0 end)
          * max(case when Q(X) then 1 else 0 end) = 1"
          is for some X:P(X) and for some X:Q(X)
          *****************************************************************
          4. What threads are using "Boolean algebra in some Rows" ?:|

          I will write that threads sorted by easy.

          query help
          How can I apply query for criteria?
          Help Needed to write select statement

          Select only records with same Id
          Newbie: Simple select with 'IN ALL'

          Help with Corelated sub-query
          Select statement

          *****************************************************************
          OracleSQLPuzzle (MyHomePage)
          http://www.geocities.jp/oraclesqlpuzzle

          sister thread
          Tabibitosan method tutorial Tabibitosan method tutorial by Aketi Jyuuzou
          • 2. Re: Boolean algebra in some Rows tutorial by Aketi Jyuuzou
            damorgan
            These forums are generally not used to post tutorials so it is likely those that might benefit from them won't find what you've posted.

            Consider helping us kick off the new Sample Code section:
            https://www.samplecode.oracle.com
            • 3. Re: Boolean algebra in some Rows tutorial by Aketi Jyuuzou
              Solomon Yakobson
              I assume in your Boolean Algebra 0 represents False and 1 represents True. If so:
              chk1 I will check each ID has only both 'aaa' and 'ccc'
              min(case when Val in('aaa','ccc') then 1 else 0 end) over(partition by ID) as chk1
              You mean chk1 will check if each ID has nothing but either 'aaa'or 'ccc'
              chk3 I will check each ID has both 'aaa' and 'ccc'.
              max(case when Val='aaa' then 1 else 0 end) over(partition by ID) * max(case when Val='bbb' then 1 else 0 end) over(partition by ID) as chk3
              I assume it is typo and you meant 'ccc' not 'bbb'.
              chk4 I will check each ID does not have only both 'aaa' and 'ccc'.
              min(case when Val in('aaa','ccc') then 0 else 1 end) over(partition by ID) as chk4
              You mean chk4 will check if each ID has something other than 'aaa' or 'ccc'. It might not have 'aaa' or 'ccc' at all.
              chk5 I will check each ID does not have at least 'aaa' or 'ccc'.
              max(case when Val in('aaa','ccc') then 1 else 0 end) over(partition by ID) as chk5
              You mean chk5 will check each ID has at least 'aaa' or 'ccc'.

              SY.
              • 4. Re: Boolean algebra in some Rows tutorial by Aketi Jyuuzou
                730428
                In chk3 you made a typo as Solomon has already write.
                chk1 I will check each ID has only both 'aaa' and 'ccc'.
                chk4 I will check each ID does not have only both 'aaa' and 'ccc'.
                So I think chk4=¬chk1 (NOT chk1). If so you should use max instead of min.
                chk2 I will check each ID has at least 'aaa' or 'ccc'.
                chk5 I will check each ID does not have at least 'aaa' or 'ccc'.
                So I think chk5=¬chk2 (NOT chk2). If so you should use min instead of max.
                SQL> select ID,
                  2  min(case when Val in('aaa','ccc') then 1 else 0 end) as chk1,
                  3  max(case when Val in('aaa','ccc') then 1 else 0 end) as chk2,
                  4  max(case when Val='aaa' then 1 else 0 end)*max(case when Val='ccc' then 1 else 0 end) as chk3,
                  5  max(case when Val in('aaa','ccc') then 0 else 1 end) as chk4,
                  6  min(case when Val in('aaa','ccc') then 0 else 1 end) as chk5
                  7  from boolEx1
                  8  group by ID
                  9  order by id;
                
                        ID       CHK1       CHK2       CHK3       CHK4       CHK5
                ---------- ---------- ---------- ---------- ---------- ----------
                         1          0          1          1          1          0
                         2          1          1          1          0          0
                         3          0          1          0          1          0
                         4          0          0          0          1          1
                Max
                [My Italian Oracle blog|http://oracleitalia.wordpress.com/2009/12/26/leggiamo-meglio-il-dizionario-dati-utilizzando-dbms_metadata/]
                • 5. Re: Boolean algebra in some Rows tutorial by Aketi Jyuuzou
                  Aketi Jyuuzou
                  OOPS my English is not good at.
                  I have fixed my English miss of previous post.

                  When I will answer similar threads,I will write these tutorial threads URL B-)