Home » SQL & PL/SQL » SQL & PL/SQL » Identify the department VP in an employee interface table.
Identify the department VP in an employee interface table. [message #447279] Fri, 12 March 2010 18:46 Go to next message
atulmathur
Messages: 5
Registered: March 2010
Junior Member
I have employee interface table something like this.
emp_id	emp_name        Job_title       supervisor_name
1	AJ              Engineer        BJ
2	CK              Analyst	        ND
3	BJ              Manager	        TR
5	TR              VP IT           JD
6	ND              S Manager       MD
7	MD	        VP Telecom      SK
8	SK              VP Eng          JR

I want to idenitfy the VP for each employee. The logic I have to apply is check for hte supervisor of each employee to see if the supervisor has designation starting with 'VP'. If no, I have check the supervisor of the supervisor and so on. I tried using a recursive query using connect_by_root but in the above example for employee ND it lists the VP as both MD and SK. I need it to show on MD who is the lower in the hierarchy. Could someone help?

I am a Java person but since my app uses the Oracle DB I am to do this task.
I am not a Oracle person so kindly pardon my ignorance.

[EDITED by LF: applied [pre] tags and fixed formatting]

[Updated on: Sat, 13 March 2010 15:53] by Moderator

Report message to a moderator

Re: Identify the department VP in an employee interface table. [message #447280 is a reply to message #447279] Fri, 12 March 2010 18:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

SEARCH this forum for solution to this FAQ
Re: Identify the department VP in an employee interface table. [message #447289 is a reply to message #447279] Sat, 13 March 2010 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.
Show us what you tried.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Identify the department VP in an employee interface table. [message #448156 is a reply to message #447279] Fri, 19 March 2010 18:35 Go to previous messageGo to next message
atulmathur
Messages: 5
Registered: March 2010
Junior Member
Thanks a lot for your input.
My SQL may not be up to the mark so the create statement below may be incorrect.

Create statement:
CREATE TABLE usp_contact (
CONTACT_UUID NOT NULL NVARCHAR2(30),
LDAP_DN NVARCHAR2(512),
ZSUPERVISOR_LDAP_DN NVARCHAR2(512),
Title NVARCHAR2(30));

Insert statement:
insert all
into usp_contact values ('AACAB3FC45574D4E9E8EF6845512D006','uid=andrea','uid=black','Manager BSD')
into usp_contact values ('247893904522FF4795CC0F828B66C171','uid=james','uid=andrea','Application Engineer')
into usp_contact values ('E9C865E438AF6F4A83608CDF6A962C7C','uid=charles','uid=albert','IT Director Bus. Sys. & Dev.')
into usp_contact values ('FECE10A09AC7734B993368D9E3B8D06B','uid=black','uid=charles','Dir Business System Dev')
into usp_contact values ('B9F788296472C946B7D6D0F1BD794C52','uid=albert','uid=joe','VP IT')
into usp_contact values ('568253BEC13A9048B344B3EB2F95D494','uid=chang','uid=jack','VP Sales & Marketing EMEA')
into usp_contact values ('7AEF4851323BB3439C4067FCA4E7E4C2','uid=marvin','uid=sean','Administrative Assistant')
into usp_contact values ('389F2485EFE93640BFD90160AA576610','uid=sean','uid=chang','VP Emerging Markets')
select * from dual;
Output Required:
LDAP_DN ZSUPERVISOR_LDAP_DN
uid=andrea uid=albert
uid=james uid=albert
uid=charles uid=albert
uid=black uid=albert
uid=albert uid=joe
uid=chang uid=jack
uid=marvin uid=sean
uid=sean uid=chang

My Query:
select connect_by_root u.ldap_dn emp, u.ldap_dn manager
from usp_contact u
where CONNECT_BY_ISLEAF= 1
connect by prior u.zsupervisor_ldap_dn = u.ldap_dn
and prior u.z_position not in ('VP Sales & Marketing EMEA', 'VP Emerging Markets', 'VP Emerging Markets');
Re: Identify the department VP in an employee interface table. [message #448157 is a reply to message #448156] Fri, 19 March 2010 18:50 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
  1  CREATE TABLE usp_contact (
  2  CONTACT_UUID NOT NULL NVARCHAR2(30),
  3  LDAP_DN NVARCHAR2(512),
  4  ZSUPERVISOR_LDAP_DN NVARCHAR2(512),
  5* Title NVARCHAR2(30))
SQL> /
CONTACT_UUID NOT NULL NVARCHAR2(30),
                      *
ERROR at line 2:
ORA-00907: missing right parenthesis


post SQL is supposed to actually work.
Sad

SQL> insert all
into usp_contact values ('AACAB3FC45574D4E9E8EF6845512D006','uid=andrea','uid=black','M  2  anager BSD')
into usp_contact values ('247893904522FF4795CC0F828B66C171','uid=james'  3  ,'uid=andrea','Application Engineer')
into usp_contact values ('E9C865E438AF6F4A83608CDF6A962C7C','uid=charles','uid=albert'  4  ,'IT Director Bus. Sys. & Dev.')
into usp_contact values ('FECE10A09AC7734B993368D9E3B8D06B','uid=black','uid=charles','D  5  ir Business System Dev')
into usp_contact values ('B9F788296472C946B7D6D0F1BD794C52','uid=albert','uid=joe','VP   6  IT')
into usp_contact values ('568253BEC13A9048B344B3EB2F95D494','uid=chang','uid=jack',  7  'VP Sales & Marketing EMEA')
into usp_contact values ('7AEF4851323BB3439C4067FCA4E7E4C2','uid=marvin','uid=sean','Ad  8  ministrative Assistant')
into usp_contact values ('389F2485EFE93640BFD90160AA576610','uid=sean  9  ','uid=chang','VP Emerging Markets')
select * from dual; 10   
Enter value for dev: 1
old   4: into usp_contact values ('E9C865E438AF6F4A83608CDF6A962C7C','uid=charles','uid=albert','IT Director Bus. Sys. & Dev.')
new   4: into usp_contact values ('E9C865E438AF6F4A83608CDF6A962C7C','uid=charles','uid=albert','IT Director Bus. Sys. 1')
Enter value for marketing: 1
old   7: into usp_contact values ('568253BEC13A9048B344B3EB2F95D494','uid=chang','uid=jack','VP Sales & Marketing EMEA')
new   7: into usp_contact values ('568253BEC13A9048B344B3EB2F95D494','uid=chang','uid=jack','VP Sales 1 EMEA')
into usp_contact values ('AACAB3FC45574D4E9E8EF6845512D006','uid=andrea','uid=black','Manager BSD')
                         *
ERROR at line 2:
ORA-12899: value too large for column "DBADMIN"."USP_CONTACT"."CONTACT_UUID"
(actual: 32, maximum: 30)


Enjoy your mystery!

[Updated on: Fri, 19 March 2010 18:53]

Report message to a moderator

Re: Identify the department VP in an employee interface table. [message #448183 is a reply to message #448156] Sat, 20 March 2010 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are close to get your result (see below) but this result is not consistent with your first requirements (why 'VP IT' which starts with VP is not in the list? Why for this one (albert) you return its manager and not nothing as it has no manager ancestor starting with VP?).

SQL> select connect_by_root u.ldap_dn emp, decode(level,1,u.zsupervisor_ldap_dn,u.ldap_dn) manager
  2  from usp_contact u
  3  where connect_by_isleaf = 1
  4  connect by prior u.zsupervisor_ldap_dn = u.ldap_dn
  5               and u.title not in 
  6                     ('VP Sales & Marketing EMEA', 'VP Emerging Markets', 'VP Emerging Markets');
EMP                  MANAGER
-------------------- --------------------
uid=albert           uid=joe
uid=andrea           uid=albert
uid=black            uid=albert
uid=chang            uid=jack
uid=charles          uid=albert
uid=james            uid=albert
uid=marvin           uid=sean
uid=sean             uid=chang

Regards
Michel
Re: Identify the department VP in an employee interface table. [message #449604 is a reply to message #448183] Tue, 30 March 2010 16:19 Go to previous messageGo to next message
atulmathur
Messages: 5
Registered: March 2010
Junior Member
Hello,
Thanks. I think that helps me get what I am looking for. I am still looking at my table to make sure the output is consistent.

I am actually looking to update the output into another column within the same table.

alter TABLE usp_contact ADD (vp_dn NVARCHAR2(200));

I am trying to update this column with this statement:

update usp_contact a set a.vp_dn =
(select u.zsupervisor_ldap_dn manager
from usp_contact u
where CONNECT_BY_ISLEAF = 1
and a.contact_uuid = u.contact_uuid
connect by nocycle prior u.zsupervisor_ldap_dn = u.ldap_dn
and u.title NOT LIKE 'VP%');

Could you please help me with the right syntax?
Re: Identify the department VP in an employee interface table. [message #449612 is a reply to message #449604] Tue, 30 March 2010 23:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am trying to update this column with this statement:

You are trying to update this column with what, in words?
How could we with which value you want to set the column with a statement you ask us to check and so you are not sure it gives the correct value.

Please FORMAT your post and query as we did (read the link I earlier provided).

Regards
Michel
Re: Identify the department VP in an employee interface table. [message #449748 is a reply to message #449612] Wed, 31 March 2010 12:31 Go to previous messageGo to next message
atulmathur
Messages: 5
Registered: March 2010
Junior Member
I am actually looking to update the output of the query into another column within the same table.
So I added another column to the table first.
alter TABLE usp_contact ADD (vp_dn NVARCHAR2(200));


I am now trying to update the output of the query you gave previously to this column with below statement:

update usp_contact a set a.vp_dn = 
(select u.zsupervisor_ldap_dn manager
from usp_contact u
where CONNECT_BY_ISLEAF	= 1
and a.contact_uuid = u.contact_uuid 
connect by nocycle	prior u.zsupervisor_ldap_dn = u.ldap_dn
and u.title NOT LIKE 'VP%');


This does not seem to work. Could you help?

Hope this helps.
Re: Identify the department VP in an employee interface table. [message #449752 is a reply to message #449748] Wed, 31 March 2010 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To get the same values as in my previous query (as I still don't know what are your exact requirements):
SQL> alter TABLE usp_contact ADD (vp_dn NVARCHAR2(20));

Table altered.

SQL> update usp_contact a
  2  set vp_dn = 
  3      ( select decode(level,1,u.zsupervisor_ldap_dn,u.ldap_dn) manager
  4        from usp_contact u
  5        where connect_by_isleaf = 1
  6        connect by prior u.zsupervisor_ldap_dn = u.ldap_dn
  7                     and u.title not in 
  8                         ('VP Sales & Marketing EMEA', 'VP Emerging Markets', 'VP Emerging Markets')
  9        start with u.ldap_dn = a.ldap_dn )
 10  /

8 rows updated.

SQL> select u.ldap_dn, u.vp_dn from usp_contact u order by 1;
LDAP_DN              VP_DN
-------------------- --------------------
uid=albert           uid=joe
uid=andrea           uid=albert
uid=black            uid=albert
uid=chang            uid=jack
uid=charles          uid=albert
uid=james            uid=albert
uid=marvin           uid=sean
uid=sean             uid=chang

8 rows selected.


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Wed, 31 March 2010 12:53]

Report message to a moderator

Re: Identify the department VP in an employee interface table. [message #449756 is a reply to message #449612] Wed, 31 March 2010 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I am actually looking to update the output of the query into another column within the same table.

This may be a bad "solution".
What happens when VP Rick is replaced by VP Sam?
Does Albert's record get changed at the same time?

Simply put & generally speaking it is a Bad Thing to store derived data as as separate column in the table.
Re: Identify the department VP in an employee interface table. [message #449760 is a reply to message #449756] Wed, 31 March 2010 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree with BlackSwan, if you need performances you should create a materialized view for this.

Regards
Michel
Re: Identify the department VP in an employee interface table. [message #449778 is a reply to message #449760] Wed, 31 March 2010 17:30 Go to previous messageGo to next message
atulmathur
Messages: 5
Registered: March 2010
Junior Member
That is not a concern for me since I am synchronizing the table with my LDAP source.

Not sure what materialized view is(as mentioned I am not a Oracle guy) but I think I can live with it.
Re: Identify the department VP in an employee interface table. [message #449788 is a reply to message #449778] Wed, 31 March 2010 23:19 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For your information here are some information about materialized views:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#i2063793
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#g1028195

Regards
Michel
Previous Topic: Recyclebin
Next Topic: very tough problem
Goto Forum:
  


Current Time: Fri Dec 09 15:08:59 CST 2016

Total time taken to generate the page: 0.25192 seconds