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.

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.

how to add child nodes to a selected node in a sequential manner?

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

join your table with

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

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.

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

its still giving the same error.

what error are you getting?

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 :)

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

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

Forced accept.

Computer101
EE Admin