Oracle Recursion

Question

I want list all SOURCE_ID if the source has destination, the query below not work probably !!

select SOURCE_ID,
        LINK_TYPE,
        DESTINATION_ID 
from LINK_TABLE
where link_table.link_type=1 
and link_table.destination_is_deleted=0
START WITH link_table.source_id='100'
CONNECT BY PRIOR link_table.source_id=link_table.destination_id

Sample data

SOURCE_ID |  DESTINATION_ID | LINK_TYPE| DESTINATION_IS_DELETED|
----------|-----------------|----------|-----------------------|
100       |      1500       |    1     |             0         |
100       |       1200      |    1     |             0         |
100       |      1300       |    1     |             1         |
1500      |       600       |    1     |             0         |
1500      |       700       |    1     |             0         |
700       |        88       |    1     |             0         |

Sorry Sir, and thank you for help me.

MAX SMART
– MAX SMART

2017-03-06 12:01:48 +00:00
Commented Mar 6, 2017 at 12:01 — MAX SMART
– MAX SMART, Commented Mar 6, 2017 at 12:01

APC · Accepted Answer · 2017-03-06 12:07:26Z

1

Assuming you want to walk the hierarchy from the root nodes to the leaves this is what you need:

select SOURCE_ID,
        LINK_TYPE,
        DESTINATION_ID 
from LINK_TABLE
where link_table.link_type=1 
and link_table.destination_is_deleted=0
START WITH link_table.source_id='100'
CONNECT BY PRIOR link_table.destination_id = link_table.source_id
/

It's just a matter of swapping the referenced columns in the CONNECT BY PRIOR clause. Unfortunately the Oracle syntax is not intuitive here: I've been using it for over twenty years and I still have to test a query to make sure I've got them the right way round :)

answered Mar 6, 2017 at 12:07

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MAX SMART · Accepted Answer · 2017-03-07 10:18:56Z

0

I changed the query structure to solve the issue.

select SOURCE_ID,DESTINATION_ID,link_table.source_class,link_table.linktype,link_table.destination_class
from LINK_TABLE
where (link_table.source_id in( select link_table.DESTINATION_ID from link_table where link_table.source_id='100'and link_table.linktype=1 and link_table.destination_isdeleted=0 ))
and (link_table.linktype=1) or (link_table.source_id='100')

answered Mar 7, 2017 at 10:18

MAX SMART

3910 bronze badges

Collectives™ on Stack Overflow

Oracle Recursion

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related