This content has been marked as final. Show 5 replies
Thanks for providing create table and insert statement. Just tell us what is your required output with clear and simple logic behind it. Take your own time to elaborate the logic behind required output, so that we may understand what do you want.
Again, don't be in hurry, we are here 24x7 and this forum is running 24x7.... :)
You cannot use connect by and start with clauses related to heirarchical query on a select having a join of multiple tables. Instead merge the tables data and then you can go for a heirarchical query.
Please post your requirement so that alternative solution can be provided.
I got the solution for this using hierrchial query
Thats good to hear you got the solution, but will you please share the query, output and logic for future readers if some other reader want to get help from your solution.
Also please close the thread too.
This is the sql query to get first parent and last child.
SELECT DISTINCT A.*
(SELECT DEST_ID,SOURCE_ID, CONNECT_BY_ISLEAF AS LEAF,level ll,CONNECT_BY_ROOT source_id AS parent_id
START WITH DEST_ID =1
CONNECT BY PRIOR source_id=dest_id) a