5 Replies Latest reply: Apr 22, 2013 10:15 AM by Solomon Yakobson RSS

    Single select statement - the best solution

    635899
      Hi,

      I am trying to find the best solution for the following problem
      I have a table called HIERARCHY with 3 columns.

      col1 col2 col3
      ------------------------
      1
      1 2
      1 2 3
      4 5
      6 7 8

      I would like to check if the table contains invalid records. Col1 , Col2 and Col3 are seen as hierarchy levels and col1 is the top level.
      For instance record 1,2,3 (level3) is valid because it exists record 1,2 (level 2). Moreover 1,2 record (level 2) is valid because it exists record 1 (top level).
      However 4,5 and 6,7,8 are not valid as higher levels are not completed.
      How can I check in a single SELECT statement if the table contains invalid records? Maybe analitical functions or regular expressions would help?!
        • 1. Re: Single select statement - the best solution
          Frank Kulash
          Hi,

          Here's one way:
          SELECT     c.*
          FROM           hierarchy  c
          LEFT OUTER JOIN      hierarchy  p  ON   p.col1     = c.col1
                                         AND  (   (    p.col2     IS NULL
                                              AND  c.col3     IS NULL
                                   )
                                  OR  (    p.col2     = c.col2
                                              AND  c.col3     IS NOT NULL
                                       )
                                  )
                                AND  p.col3     IS NULL
          WHERE   p.col1     IS NULL
          AND     COALESCE (c.col2, c.col3)     IS NOT NULL
          ;
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}

          Edited by: Frank Kulash on Apr 22, 2013 10:58 AM
          • 2. Re: Single select statement - the best solution
            Solomon Yakobson
            One way to list offending rows:
            with t as (
                       select 1 col1,null col2,null col3 from dual union all
                       select 1,2,null from dual union all
                       select 1,2,3 from dual union all
                       select 4,5,null from dual union all
                       select 6,7,8 from dual
                      )
             select  *
               from  t
            minus
             select  *
               from  t
               start with col1 is not null and col2 is null and col3 is null
               connect by (col1 = prior col1 and col2 is not null and level = 2)
                       or (col2 = prior col2 and col3 is not null and level = 3)
            /
            
                  COL1       COL2       COL3
            ---------- ---------- ----------
                     4          5
                     6          7          8
            
            SQL> 
            SY.
            • 3. Re: Single select statement - the best solution
              Manik
              May not be efficient, but my solution tries to generate the pascal triangle and then tried to do a minus/not in operation.

              assuming your reference triangle as :
              1
              12
              123
              1234
              12345
              .......
              WITH t AS
                      (SELECT 1 col1, NULL col2, NULL col3 FROM DUAL
                       UNION ALL
                       SELECT 1, 2, NULL FROM DUAL
                       UNION ALL
                       SELECT 1, 2, 3 FROM DUAL
                       UNION ALL
                       SELECT 4, 5, NULL FROM DUAL
                       UNION ALL
                       SELECT 6, 7, 8 FROM DUAL)
              SELECT col1,
                     col2,
                     col3,
                     CASE
                        WHEN (col1 || col2 || col3) NOT IN
                                (SELECT SUBSTR (REPLACE (str, ','), 1, r) str
                                   FROM (    SELECT ROWNUM r,
                                                    (    SELECT listagg (ROWNUM, ',')
                                                                   WITHIN GROUP (ORDER BY 1)
                                                           FROM DUAL
                                                     CONNECT BY ROWNUM <= 5)
                                                       str
                                               FROM DUAL
                                         CONNECT BY ROWNUM <= 5) t) THEN
                           'NO'
                        ELSE
                           'YES'
                     END
                        flg
                FROM t a;
              Output:
              COL1     COL2     COL3     FLG
              --------------------------------------------------------
              1               YES
              1     2          YES
              1     2     3     YES
              4     5          NO
              6     7     8     NO
              Cheers,
              Manik.

              Edited by: Simplified the solution
              • 4. Re: Single select statement - the best solution
                pollywog
                I added a couple more records is this how it should respond with this type of data
                /* Formatted on 4/22/2013 11:06:32 AM (QP5 v5.185.11230.41888) */
                WITH t
                     AS (SELECT 1 col1, NULL col2, NULL col3 FROM DUAL
                         UNION ALL
                         SELECT 1, 2, NULL FROM DUAL
                         UNION ALL
                         SELECT 1, 2, 3 FROM DUAL
                         UNION ALL
                         SELECT 4, 5, NULL FROM DUAL
                         UNION ALL
                         SELECT 6, 7, 8 FROM DUAL
                         UNION ALL
                         SELECT 11, NULL, NULL FROM DUAL
                         UNION ALL
                         SELECT 11, 13, NULL FROM DUAL
                         UNION ALL
                         SELECT 11, 15, 16 FROM DUAL
                         UNION ALL
                         SELECT 11, 13, 14 FROM DUAL
                         UNION ALL
                         SELECT 11, 12, NULL FROM DUAL),
                     t2
                     AS (SELECT t.*,
                                CASE
                                   WHEN col1 IS NOT NULL AND col2 IS NULL AND col3 IS NULL
                                   THEN
                                      1
                                   WHEN     col1 IS NOT NULL
                                        AND col2 IS NOT NULL
                                        AND col3 IS NULL
                                   THEN
                                      2
                                   WHEN     col1 IS NOT NULL
                                        AND col2 IS NOT NULL
                                        AND col3 IS NOT NULL
                                   THEN
                                      3
                                END
                                   lvl
                           FROM t)
                  SELECT col1,
                         col2,
                         col3,
                         CASE
                            WHEN lvl = 1
                            THEN
                               1
                            WHEN     lvl = 2
                                 AND LAG (col1)
                                     OVER (ORDER BY col1, col2 NULLS FIRST, col3 NULLS FIRST) =
                                        col1
                            THEN
                               1
                            WHEN     lvl = 3
                                 AND LAG (col1)
                                     OVER (ORDER BY col1, col2 NULLS FIRST, col3 NULLS FIRST) =
                                        col1
                                 AND LAG (col2)
                                     OVER (ORDER BY col1, col2 NULLS FIRST, col3 NULLS FIRST) =
                                        col2
                            THEN
                               1
                            ELSE
                               0
                         END
                            valid_in
                    FROM t2
                ORDER BY col1, col2 NULLS FIRST, col3 NULLS FIRST
                
                
                COL1     COL2     COL3     VALID_IN
                1               1
                1     2          1
                1     2     3     1
                4     5          0
                6     7     8     0
                11               1
                11     12          1
                11     13          1
                11     13     14     1
                11     15     16     0
                • 5. Re: Single select statement - the best solution
                  Solomon Yakobson
                  Solomon Yakobson wrote:
                  One way to list offending rows:
                  Will not work for cases where not null,null,not null:
                  SQL> with t as (
                    2             select 1 col1,null col2,null col3 from dual union all
                    3             select 1,2,null from dual union all
                    4             select 1,2,3 from dual union all
                    5             select 4,5,null from dual union all
                    6             select 6,7,8 from dual union all
                    7             select 6,7,99 from dual union all
                    8             select 6,null,55 from dual union all
                    9             select 6,null,null from dual union all
                   10             select 9,null,10 from dual union all
                   11             select null,11,null from dual union all
                   12             select null,null,12 from dual union all
                   13             select null,null,null from dual
                   14            )
                   15   select  *
                   16     from  t
                   17  minus
                   18   select  *
                   19     from  t
                   20     start with col1 is not null and col2 is null and col3 is null
                   21     connect by (col1 = prior col1 and col2 is not null and level = 2)
                   22             or (col2 = prior col2 and col3 is not null and level = 3)
                   23  / 
                  
                        COL1       COL2       COL3
                  ---------- ---------- ----------
                           4          5
                           6                    55
                           9                    10
                                     11
                                                12
                  
                  
                  6 rows selected.
                  
                  SQL> 
                  Needs one more condition:
                  with t as (
                             select 1 col1,null col2,null col3 from dual union all
                             select 1,2,null from dual union all
                             select 1,2,3 from dual union all
                             select 4,5,null from dual union all
                             select 6,7,8 from dual union all
                             select 6,7,99 from dual union all
                             select 6,null,55 from dual union all
                             select 6,null,null from dual union all
                             select 9,null,10 from dual union all
                             select null,11,null from dual union all
                             select null,null,12 from dual union all
                             select null,null,null from dual
                            )
                   select  *
                     from  t
                  minus
                   select  *
                     from  t
                     start with col1 is not null and col2 is null and col3 is null
                     connect by (col1 = prior col1 and col2 is not null and col3 is null and level = 2)
                             or (col2 = prior col2 and col3 is not null and level = 3)
                  /
                  
                        COL1       COL2       COL3
                  ---------- ---------- ----------
                           4          5
                           6          7          8
                           6          7         99
                           6                    55
                           9                    10
                                     11
                                                12
                  
                  
                  8 rows selected.
                  
                  SQL> 
                  SY.