We help IT Professionals succeed at work.

how can i create a hirarchical tree having nodes as A to Z, and each node display the names of employees starting with that alphabet.

632 Views
Last Modified: 2012-05-05
how can i create a tree having nodes as alphabets A-Z, and each alphabet listing the sorted list of employee names?. what should i give as connect by prior and start with
Comment
Watch Question

I am not sure how CONNECT BY would help you in this since to the best of my knowledge, it is used for retrieval of data that is stored in a hierarchical fashion within the DB itself. E.g. Employees and their managers, etc. whereas in your case, you are trying to display sequential data in a hierarchical fashion.

Author

Commented:
how to add child nodes to a selected node in a sequential manner?
Peter KwanAnalyst Programmer

Commented:
I have come up with the following SQL, please see whether that is what you want:

select lpad('|-- ', 4*(level-1)) || employee_name from
(select substr(emp_name,1,1) employee_name, NULL employee_start from your_table
union
select emp_name employee_name, substr(emp_name,1,1) employee_start from your_table
order by employee_name
)
start with employee_start is null
connect by employee_start = prior employee_name

Commented:
join your table with

select chr(ascii('A')+level-1) from dual connect by level <= 26

Author

Commented:
select lpad('|-- ', 4*(level-1)) || employee_name from
(select substr(emp_name,1,1) employee_name, NULL employee_start from your_table
union
select emp_name employee_name, substr(emp_name,1,1) employee_start from your_table
order by employee_name
)
start with employee_start is null
connect by employee_start = prior employee_name

.......this is giving me an error...invalid query.
anand_2000vOracle DBA
CERTIFIED EXPERT

Commented:
select lpad('|-- ', 4*(level-1)) || employee_name from
(select substr(emp_name,1,1) employee_name, NULL employee_start from your_table
union
select emp_name employee_name, substr(emp_name,1,1) employee_start from your_table
order by employee_name
)
start with employee_start is null
connect by prior employee_start = employee_name

if it works give the points to pkwan

Author

Commented:
its still giving the same error.
Peter KwanAnalyst Programmer

Commented:
what error are you getting?

Author

Commented:
I am having a table named emp_info_hdr ..so as per your instruction i changed my query as

select lpad('|-- ', 4*(level-1)) || employee_name from
(select substr(emph_first_name,1,1) employee_name, NULL employee_start from emp_info_hdr
union
select emph_first_name employee_name, substr(emph_first_name,1,1) employee_start from emp_info_hdr
order by employee_name
)
start with employee_start is null
connect by prior employee_start = employee_name

now i am getting error FRM-47313..invalid query for hierarchical tree
@konektor - Would appreciate if you could explain how to use that SQL in context with the emp table bundled in the SCOTT schema :)
anand_2000vOracle DBA
CERTIFIED EXPERT

Commented:
You are trying to use this in forms.
you require

-->Initial state
-->Node tree depth
-->Label for the node
-->Icon for the node
-->Data

Therefore try this command

select  1,4*(level-1)) ,'Names',null, employee_name from
(select substr(emph_first_name,1,1) employee_name, NULL employee_start from emp_info_hdr
union
select emph_first_name employee_name, substr(emph_first_name,1,1) employee_start from emp_info_hdr
order by employee_name
)
start with employee_start is null
connect by prior employee_start = employee_name
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
anand_2000vOracle DBA
CERTIFIED EXPERT

Commented:
IMHO harisrashid has given the correct answer. Kindly award the points to him, if the asker does not respond.
Forced accept.

Computer101
EE Admin
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.