Here is a quick example to help you understand how oracle Start With analytical function works. The start with .. connect by clause can be used to select data that has a hierarchical relationship. Usually, it is some sort of parent child relationship like supervisor and an employee.
Let’s assume we have a table called employees. I would like to know employees that directly report to supervisor Id 1122456 –simple, right?
Now I would like to know all employees that report to supervisor Id 1122456 (directly or indirectly).
Notice that with the 1st sql we had 3 rows total while using Start With gave us 4 rows back. If you look at the last row on the 2nd select, you notice that employee 0120033 reports to 0120022 who in return reports to 1122456.
Title | Under | Posted on |
---|---|---|
Component interface Error: no rows exist for the specified keys | PeopleSoft Technical | 03/15/2019 - 3:54am |
ADD 24 months starting from current month.(peoplesoft) | PeopleSoft Functional | 07/29/2018 - 8:44pm |
TRC values dropdown | PeopleSoft Technical | 04/04/2018 - 12:54am |
how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode | PeopleSoft Technical | 09/11/2017 - 4:49am |
Comments
Very nicely explained
Nice and simple explanation. Thanks.
So good. Thanks
Hi,
my problem with connect by is more difficult. Let say we have a table for bils of material decribing following structure:
prod
|
|-> semif1
| |
| |-> semif3
| | |
| | |->comp4
| |
| |-> comp2
|
|-> semif2
| |
| |->comp3
|
|-> comp1
where prod is final product, semifx are semifinished items on different levels in stucture and compx are components on different levels.
The table for this structure looks like follow:
prod mater
-----------------
prod semif1
prod semif2
prod comp1
semif1 semif3
semif1 comp2
semif2 comp3
semif3 comp4
there is also another table where are stored information about acqusition method of particular items. Let say acq. method for prod and all semifinished items is 1=manufactured and acq.method for components is 2=purchased.
What I would need is to exclude items semif3 and comp4 from result when acq.method for semif1 is changed on 2. Is somethink like this possible in simple SQL?
This is awesome!!!
love the simple explanation!
Thanks heaps!
Simple and straight to the point explanation, thanks.
Awesome!!! u have done a great job.. Simple and effective explanation.. Keep it up...
Thanks
Very simple and well explainaition.
Thanks a lot .
Yes
Nice Explanation.Thanks !!!
good one
gr8 explanation.. thanks buddy
superrrrrr explaination... love this :)
After looking into many posts in various websites at last i got it here..
Really nice and simple explanation. Was struggling to understand this concept. And all other websites has more confusing explanations and examples. this one made this concept so clear and easy! Thanks a lot!!
itni saralta se samjhaya hai ke kya kahna....bahut sundar....very nicely explained. I was looking for this kind of explanation only....