3 Replies Latest reply: Dec 18, 2013 6:56 PM by jihuyao RSS

    Unable to understand where clause

    Shuumail


      Hi Gurus

       

      I have tried to understand the following where clause but still facing problem to understand it and I really appreciate if somebody explain it to me step by step and in detail. Thanks in advance:

      Where Clause

      WHERE NOT (   min_age = min_min_age

                  OR            (min_age - 1, rule_id, status_code)

                   IN (  SELECT  max_age,     rule_id, status_code

                         FROM    got_analytics

                      )

                  )

       

      Regards

      Shu

        • 1. Re: Unable to understand where clause
          Frank Kulash

          Hi, Shu,

          979596 wrote:

           


          Hi Gurus

           

          I have tried to understand the following where clause but still facing problem to understand it and I really appreciate if somebody explain it to me step by step and in detail. Thanks in advance:

          Where Clause

          WHERE NOT (   min_age = min_min_age

                      OR            (min_age - 1, rule_id, status_code)

                       IN (  SELECT  max_age,     rule_id, status_code

                             FROM    got_analytics

                          )

                      )

           

          Regards

          Shu

          What does the sub-query do?

          SELECT  max_age,     rule_id, status_code

          FROM    got_analytics

          It produces a list of all the max_ages, rule_ids and status codes in your table.

          The WHERE clause checks if min_age-1, rule_id, and status_code from the current row are on that last.  In other words, it checks to see if min_age is 1 day later than max_age fro some row in the table that has the same rule_id and status_code.  If it is on that list, or if min_age is equal to min_min_age, then the row should NOT be chosen.

          • 2. Re: Unable to understand where clause
            Mike Kutz
            not ( -- simple
              min_age = min_min_age  -- simple
              or -- simple
              (min_age-1, rule_id, status_cod) in (select max_age,rule_id,status_cod from analytics) -- complex
            )
            

             

            Description for COMPLEX line

            The triplicate (min_age-1, rule_id, status_code)

            needs to match any of the triplicates produced by:

              (select
                  max_age,rule_id,status_code -- your triplicate
                from analytics)
            

             

             

            You'll have to double check my logic, but I think that the 'complex' line can be re-written as an EXISTS clause

            EXISTS ( select null
                     from analytics A
                     where A.max_age = X.min_age - 1
                       and A.rule_id = X.rule_id
                       and A.status_code = X.status_code )
            

             

            MK

            • 3. Re: Unable to understand where clause
              jihuyao

              Assuming f1, f2 boolean flags in each row, only when f1=0 and f2=0 (false) then return the row.

               

              It may help in the following

               

              SQL> ed

              Wrote file afiedt.buf

               

                1  select count(*) from emp e, dept d

                2* where not (e.deptno=d.deptno)

              SQL> /

               

                COUNT(*)

              ----------

                      42

               

              SQL> ed

              Wrote file afiedt.buf

               

                1  select count(*) from emp e, dept d

                2* --where not (e.deptno=d.deptno)

              SQL> /

               

                COUNT(*)

              ----------

                      56