User login
Sponsored links
Recent blog posts
- How to resubmit on Integration Broker message with DONE status
- Message cannot be changed. Message referenced in runtime tables (Doc ID 660969.1)
- PeopleSoft Object Type List
- PeopleSoft Data Integrity Error (124,85)
- Unwanted Save Warnings in Display Only Pages
- Breadcrumb SQL to find the Navigation path for the component in the PIA
- Application Designer Index naming convention
- How to create Trigger in DB2
- Emplid increment automatically
- How can we romove multiple duplicate rows of data printing under each Print command in SQR Program?
START WITH and CONNECT BY in Oracle
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?
FROM ps_employees
WHERE supervisor_id = '1122456';
EMPLID SUPERVISOR_ID
0119676 1122456
0112356 1122456
0120022 1122456
Now I would like to know all employees that report to supervisor Id 1122456 (directly or indirectly).
FROM ps_employees
START WITH supervisor_id = '1122456'
CONNECT BY PRIOR emplid = supervisor_id;
EMPLID SUPERVISOR_ID
0119676 1122456
0112356 1122456
0120022 1122456
0120033 0120022
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.
- Lepa's blog
- Login to post comments
- 82098 reads
Unanswered Forum Posts
| Title | Under | Posted on |
|---|---|---|
| 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 |
| How to add custom process in Paycycle PeopleSoft Accounts payable. | PeopleSoft Technical | 06/07/2017 - 4:39am |
Stay updated
About Lepa
Job title
Lead Application Developer
Poll
New forum topics
- ADD 24 months starting from current month.(peoplesoft)
- TRC values dropdown
- OHS is rendering XML instead of the view or data
- Search Results
- PS Query - Need Dependency and Prunable examples
- PS HCM AWE - Transaction routing to Requestor for approval, when he is the Delegatee by his 1 up Manager
- how to find missing sequence in GRID and print the mising sequence number while saving through peoplecode
- How to add custom process in Paycycle PeopleSoft Accounts payable.
Recent comments
- Re: PeopleCode Check Number and Check Letter Functions
11 weeks 12 hours ago - Re: nVision Tabular Report through PIA with prompts
16 weeks 4 hours ago - Re: PeopleSoft PeopleTools Meta-Tables
16 weeks 6 hours ago - Re: PS Query - Need Dependency and Prunable examples
17 weeks 5 days ago - Re: Search Results
18 weeks 2 days ago - Re: How to schedule appengine using peoplecode
18 weeks 4 days ago - Re: Mark worked in Worklist page
20 weeks 5 days ago - Re: Calculate the sum for a grid field with out using row ...
22 weeks 1 day ago - Re: Invalid SNAC client version
25 weeks 1 day ago - Re: Trace Application Engine Processes Using Process Definitions
30 weeks 6 days ago
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....