[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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.

Posted on 2007-09-29
16
Medium Priority
?
603 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
0
Comment
Question by:fyzalkabu
  • 4
  • 3
  • 2
  • +4
14 Comments
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 19986404
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.
0
 

Author Comment

by:fyzalkabu
ID: 19986442
how to add child nodes to a selected node in a sequential manner?
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 19986644
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
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 9

Expert Comment

by:konektor
ID: 19986998
join your table with

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

Author Comment

by:fyzalkabu
ID: 19988880
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.
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 19988956
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
0
 

Author Comment

by:fyzalkabu
ID: 19989037
its still giving the same error.
0
 
LVL 16

Expert Comment

by:Peter Kwan
ID: 19989179
what error are you getting?
0
 

Author Comment

by:fyzalkabu
ID: 19989266
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
0
 
LVL 18

Expert Comment

by:Jinesh Kamdar
ID: 20005829
@konektor - Would appreciate if you could explain how to use that SQL in context with the emp table bundled in the SCOTT schema :)
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 20032660
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
0
 
LVL 10

Accepted Solution

by:
harisrashid earned 2000 total points
ID: 20038954
hi,

the alphabets A - Z are not a tree hierachy.
You can sort the names alphabetically.

Regards,

Haris Rashid
0
 
LVL 13

Expert Comment

by:anand_2000v
ID: 20190907
IMHO harisrashid has given the correct answer. Kindly award the points to him, if the asker does not respond.
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20286418
Forced accept.

Computer101
EE Admin
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Online collaboration is quickly becoming embedded in the workplace, and its benefits are tangible. See what the current landscape looks like and what the future holds for collaboration tools and the future of work.
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Via a live example, show how to take different types of Oracle backups using RMAN.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question