Oracle - Employee's parent organization

Hi,

I have an employee and I need to extract it's ancestor's organization.
he re are the two tables currently available:

hr_all_organization_units
per_dept_tree_node_rf_v

for a given employee I like to extract the parent organization.

Thanks in advance
shmzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
you're going to have to do a little more work than that.

what are the columns of those tables and what are the relationships between them?
jsaunCommented:
You need to provide more details, like what are the columns in those tables.

Generally, if you want to walk up or down a tree within a table (like employee has manager, so find the chain of managers up), you can use CONNECT BY PRIOR.

Here's a fair example of that:
http://www.java2s.com/Tutorial/Oracle/0040__Query-Select/startwithjobMANAGERandconnectbyprior.htm
shmzAuthor Commented:
per_all_assignment
assignment_id, organization_id,person_id
1122, 24, 12345

hr_all_organization_units
organization_id, name , effective_start_date , effective_end_date
24, ABC, 2014-01-01, 4712-12-31


per_dept_tree_node_rf_v
organization_id, ancestor_tree_node_id, ancestor_organization_ID, distance,is_leaf
24 ,111111OBBB56, 72 , 3, Y
24, 1111B33444486, 19, 2, Y
24, 5555555555550, 30, 1, Y
24, 5555555555550, 24, 0, Y
24,   , 5, Y
24, 2332434545E8F, 64, 4, Y

so I like to know who is the ancestor organization for person_id: 12345

let me know if you need more details
sdstuberCommented:
You haven't explained the relationships.

I'm going to guess that a distance of 1 means that row is the parent of distance 0.

If so then try this...

select * from per_dept_tree_node_rf_v a, per_all_assignment p
where p.person_id = 12345
and a.organization_id = p.organization_id
and a.distance = 1;

or using join key word

select a.* from per_dept_tree_node_rf_v a inner join per_all_assignment p
on a.organization_id = p.organization_id
and a.distance = 1
where p.person_id = 12345;


if those don't work, please explain table relations and what you expect the result to be and I'll figure out the query to give you that result

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shmzAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.