Alternative to 'connect by prior'

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
uchhavi
Veteran Member
Posts: 80
Joined: 2018-05-15 14:07

Alternative to 'connect by prior'

Post by uchhavi » 2019-11-11 13:31

Hello.

I have a Stock Master table and there I have list of Procured Items and Assembled Items.
Assembled Items can comprise of other assembled Items as well as some procured Items.

I want to run a query in which, when I select an Assembled Item, the output shows me all the items that it is comprised off.. like a Bill of Material. How can I make it possible... In SQL it can be done using the 'Connect by Prior' function (as advised by someone). But how to do the same in AppGini.

Please help.

Just an Example
SQL> insert into e2
select 7566,7902 from dual union
select 7566,7788 from dual union
select 7698,7499 from dual union
select 7698,7521 from dual union
select 7698,7844 from dual union
select 7698,7900 from dual union
select 7698,7654 from dual union
select 7782,7934 from dual union
select 7788,7876 from dual union
select 7839,7566 from dual union
select 7839,7698 from dual union
select 7839,7782 from dual union
select 7902,7369 from dual union
select 7839,7840 from dual;

SQL> select * from e2 order by mgr;
MGR EMPNO
---------- ----------
7566 7902
7566 7788
7698 7654
7698 7844
7698 7521
7698 7499
7698 7900
7782 7934
7788 7876
7839 7566
7839 7698
7839 7782
7839 7840
7902 7369


select level,
MGR, EMPNO
from e2
start with MGR = 7566
connect by prior EMPNO=MGR
order by MGR

LEVEL MGR EMPNO
---------- ---------- ----------
1 7566 7788
1 7566 7902
2 7788 7876
2 7902 7369
Best regards,

Chhavi Jain


Post Reply