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

# Need output as below

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
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
Currently Being Moderated
``````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
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
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
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
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
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
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  *****
SELECT  dest
FROM    t1
WHERE   dest     IS NOT NULL
)
CONNECT BY     src  = PRIOR dest
AND     type != 2
;``````
• ###### 6. Re: Need output as below
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
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
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
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