This discussion is archived
7 Replies Latest reply: Apr 1, 2013 3:54 AM by jeneesh RSS

Need output as below

NSK2KSN Journeyer
Currently Being Moderated
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_Arp Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points