7 Replies Latest reply: Apr 1, 2013 5:54 AM by jeneesh RSS

    Need output as below

    NSK2KSN
      WITH t1
           AS (SELECT 3 dest, 1 src FROM DUAL
               UNION ALL
               SELECT 4, 3 FROM DUAL
               UNION ALL
               SELECT 6, 7 FROM DUAL
               UNION ALL
               SELECT 8, 6 FROM DUAL
               UNION ALL
               SELECT 5, 8 FROM DUAL
               UNION ALL
               SELECT 2, 5 FROM DUAL)
      SELECT src, dest
        FROM t1;
      
      src   dest
      1     3
      3     4
      7     6
      6     8
      8     5
      5     2
      
      
      WITH loc
           AS (SELECT 1 loc, 1 TYPE FROM DUAL
               UNION ALL
               SELECT 2, 2 FROM DUAL
               UNION ALL
               SELECT 3, 1 FROM DUAL
               UNION ALL
               SELECT 4, 2 FROM DUAL
               UNION ALL
               SELECT 5, 2 FROM DUAL
               UNION ALL
               SELECT 6, 1 FROM DUAL
               UNION ALL
               SELECT 7, 1 FROM DUAL
               UNION ALL
               SELECT 8, 1 FROM DUAL)
      SELECT *
        FROM loc;
      
      loc type
      1     1
      2     2
      3     1
      4     2
      5     2
      6     1
      7     1
      8     1
      
      
      
      Expected Output
      src   dest
      1     3
      3     4
      7     6
      6     8
      8     5
      
      
      basically I want to start with src and stop at the first loc of type 2
      
      1 -> 3 -> 4 => in this loc type of 1 is (1) -> loc type of 3 is (1) -> loc type of 4 is (2) and expected output is 1 -> 3 -> 4
      
      next
      
      7 -> 6 -> 8 -> 5 -> 2 in this loc type of 7 is (1) -> loc type of 6 is (1) -> loc type of 8 is (1) -> loc type of 5 is (2) -> loc type of 2 is (2) and expected output is 7 -> 6 -> 8 -> 5 ( here 5 is the first loc of type 2)
        • 1. Re: Need output as below
          _Karthick_
          You need to have a Ordering column in table T1 that would define the travelling path for column SRC
          • 2. Re: Need output as below
            jeneesh
            From your data
            SQL> with dtls as
              2  (
              3  select src,dest,type
              4  from t1 t,loc l
              5  where t.dest = l.loc
              6  )
              7  select *
              8  from dtls
              9  start with src not in
             10    (select dest from t1)
             11  connect by prior dest = src
             12  and prior type = 1; --"Or use - prior type != 2 "
            
                   SRC       DEST       TYPE
            ---------- ---------- ----------
                     1          3          1
                     3          4          2
                     7          6          1
                     6          8          1
                     8          5          2
            Edited by: jeneesh on Mar 28, 2013 11:38 AM
            • 3. Re: Need output as below
              Frank Kulash
              Hi,
              NSK2KSN wrote:
              basically I want to start with src and stop at the first loc of type 2
              Assuming the src/dest realtionship in t1 defiines what "first" means:
              WITH     t1_loc     AS
              (
                   SELECT     t1.src, t1.dest
                   ,     loc.type
                   FROM     t1
                   JOIN     loc     ON   loc.loc  = t1.src
              )
              SELECT  src
              ,     dest
              FROM     t1_loc
              START WITH     src NOT IN (
                                              SELECT  dest
                                    FROM    t1
                                    WHERE   dest     IS NOT NULL
                                          )     
              CONNECT BY     src  = PRIOR dest
                   AND     type != 2
              ;
              The output is what you requested:
                     SRC       DEST
              ---------- ----------
                       1          3
                       3          4
                       7          6
                       6          8
                       8          5
              The sub-query isn't absolutely necessary, but it may make the query run faster than doing the join and the CONNECT BY in the same query.
              • 4. Re: Need output as below
                NSK2KSN
                Thanks for your output
                
                I also need below output:
                
                SRC       DEST
                ---------- ----------
                         1          3
                         3          4
                         7          6
                         6          8
                         8          5
                
                
                SRC       DEST
                ---------- ----------
                         1          4
                         7          5
                
                
                for which I have used below query, but looking if I can get the required output in another query
                
                SELECT SRC, DEST,PAT, REGEXP_SUBSTR(PAT,'[0-9]+') REQ_SRC,REGEXP_SUBSTR(PAT,'[0-9]$+') REQ_DEST FROM (
                WITH    t1_loc    AS
                (
                   SELECT    t1.src, t1.dest
                   ,    loc.type
                   FROM    T1
                   JOIN      LOC   ON   loc.loc  = t1.src
                )
                SELECT  src
                ,    dest, SYS_CONNECT_BY_PATH(SRC,'/'), SYS_CONNECT_BY_PATH(SRC,'/')||'/'||dest PAT,
                DEST - LEAD(SRC,1,1) OVER (ORDER BY 1) DIFF
                FROM    t1_loc
                START WITH    src NOT IN (
                                            SELECT  dest
                                  FROM    t1
                                  WHERE   dest    IS NOT NULL
                                            
                CONNECT BY    src  = PRIOR dest
                   AND    type != 2) WHERE DIFF <> 0;
                • 5. Re: Need output as below
                  Frank Kulash
                  Hi,

                  Always explain how you get the results you want from the sample data.
                  When there is a chain of rows, where the dest of ro N equals src of row N+1, do you want the src from the 1st row, and the dest from the last row?
                  If so:
                  WITH     t1_loc     AS
                  (
                       SELECT     t1.src, t1.dest
                       ,     loc.type
                       FROM     t1
                       JOIN     loc     ON   loc.loc  = t1.src
                  )
                  SELECT  CONNECT_BY_ROOT src     AS src          -- ***** CHANGED *****
                  ,     dest
                  FROM     t1_loc
                  WHERE     CONNECT_BY_ISLEAF     = 1          -- *****  ADDED  *****
                  START WITH     src NOT IN (
                                                  SELECT  dest
                                        FROM    t1
                                        WHERE   dest     IS NOT NULL
                                              )     
                  CONNECT BY     src  = PRIOR dest
                       AND     type != 2
                  ;
                  • 6. Re: Need output as below
                    NSK2KSN
                    Frank
                    with your below code
                    
                    WITH     t1_loc     AS
                    (
                         SELECT     t1.src, t1.dest
                         ,     loc.type
                         FROM     t1
                         JOIN     loc     ON   loc.loc  = t1.src
                    )
                    SELECT  src
                    ,     dest
                    FROM     t1_loc
                    START WITH     src NOT IN (
                                                    SELECT  dest
                                          FROM    t1
                                          WHERE   dest     IS NOT NULL
                                                )     
                    CONNECT BY     src  = PRIOR dest
                         AND     type != 2
                    ;
                    
                    SRC       DEST
                    ---------- ----------
                             1          3
                             3          4
                             7          6
                             6          8
                             8          5
                    
                    
                    I need below output
                    
                    SRC       DEST
                    ---------- ----------
                             1          4
                             3          4
                             7          5
                             6          5
                             8          5
                    
                    My destination should be same for the entire cycle
                    
                    1-> 3 -> 4 => 1 4, 3 4 
                    7-> 6 -> 8 -> 5 => 7 5, 6 5, 8 5
                    • 7. Re: Need output as below
                      jeneesh
                      This?
                      SQL> WITH       t1_loc  AS
                        2  (
                        3     SELECT  t1.src, t1.dest
                        4     ,       loc.type
                        5     FROM    t1
                        6     JOIN    loc     ON   loc.loc  = t1.src
                        7  ),
                        8  tree_data as
                        9  (
                       10     SELECT  src
                       11     ,       dest
                       12     ,       level lvl
                       13     ,       connect_by_root(src) rt
                       14     FROM    t1_loc
                       15     START WITH      src NOT IN (
                       16                                    SELECT  dest
                       17                            FROM    t1
                       18                            WHERE   dest     IS NOT NULL
                       19                                )
                       20     CONNECT BY      src  = PRIOR dest
                       21     AND     type != 2
                       22  )
                       23  select src,
                       24     first_value(dest) over(partition by rt order by lvl desc) dest
                       25  from tree_data
                       26  order by rt,lvl;
                      
                             SRC       DEST
                      ---------- ----------
                               1          4
                               3          4
                               7          5
                               6          5
                               8          5