Looks like Siva Kumar Sunku did not reply to your queries.
This challenge is one of my favourites for years. So, let me try to answer the queries.
In one sentence, challenge is :
Find all employees in a given employee's own org structure
i.e. Self
+ Direct Reports
+ Indirect reports (all direct reports of your direct reports or indirect reports)
Quote:
Input string to ??? Emp_id, or Superior_id
Both. Initially it will be used against Emp_id and then against Superior _id
Quote:
And where is the Superior_Name coming from?
Every Superior is an Employee. So Superior_Name comes from EMP_NAME
This is a SELF-LOOP table. It is a parent/child of itself.
Siva Kumar Sunku,
(i) ORACLE has a SQL Clause named START to handle self loops easily. I dont think ANSI SQL has any such feature. If Oracle specific responses are acceptable, I can post it here.
(ii) Without a feature like the above, solution will have to use a CURSOR based logic with loops. Solution would go something like below:
Code:
Ids_in_the_list = 1
Last_id_index = 0
While (Last_id < Ids_in_the_list )
select a.emp_id
from emp a
where a.superior_id = Id_list(Last_id_index)
' Add these outputs to the Id_list and increment
Ids_in_the_list by number of rows added.
increment Last_id_index by 1
While-end
For i = 1 to Id_in_the_list
select a.emp_id, b.emp_name as superior_name, b.emp_name
from emp a, emp b
where a. emp_id = Id_list(i) AND b.emp_id = a.superior_id
' This is the result
Next i
Better solutions may be possible, if temporary tables are used for Id_List. I am not sure whether following would work But if it does , it would be great.
Code:
' insert temp1 table emp_id value(Input_id)
While (rowcount >0 )
select a.emp_id
from emp a
where a.superior_id in (select * from temp1)
into temp1
#2. Algorithm to find the whether Linked list is Circular or not in Order of N.
Note:- Linked List may be partial circular.
Whether the linked ls circular or not can be checked by checking Id_List contents before inserting a new one to it. Same applies to temp1 in the temp table version.
I did not understand the following:
Quote:
..... not in Order of N. Note:- Linked List may be partial circular.
What is the Order of N here ? I never heard/seen anybody using Order term in Linked List context. Are you talking about the depth of the org structure here in this example ? If so, it can be easily detected - as it is number of while loop execution. START in Oracle also has a feature to get that info.
Also, I did not understand , why/how the list can be partial circular ? In the employee table example, I could not see such possibility.
Anyway, circualr linked list can be detected as mentioned above, and can be skipped. So if they are not added to the Id_list (or temp1) secodn time, it will not cause any problems.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
