recursive query

how do i write a recursive query on oracle 10g?
Columns are OfficeID,Officename,DeptID,Deptname
Office is the parent and dept the child
should output similar to below
Office
----- Dept
----- Dept
----- Dept
Office
----- Dept
----- Dept
Office
----- Dept
----- Dept
----- Dept
----- Dept
LVL 25
dgrafxAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
here's one method  - same idea as above, but generating the double list

SELECT   name
    FROM (SELECT DISTINCT officeid, s, DECODE(s,  1, officename,  2, '----- ' || deptname) name
            FROM yourtable,
                 (SELECT 1 s FROM DUAL
                  UNION ALL
                  SELECT 2 FROM DUAL))
ORDER BY officeid, s, name
0
 
slightwv (䄆 Netminder) Commented:
This is a hierarchical query in Oracle.  

Using connect by prior.  

There is a manager/employy example here:
http://psoug.org/reference/connectby.html
0
 
sdstuberCommented:
I assume you have an officeid column in your dept table to indicate the parent.

Since you don't really have a recursive hierarchy here I think you can forgo the connect by and simply union and sort the results.

SELECT   DECODE(s, 2, '----- ') || officename name
    FROM (SELECT 1 s, officeid, officename FROM office
          UNION ALL
          SELECT 2 s, officeid, deptname FROM dept)
ORDER BY officeid, s, officename
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
if you would have multiple levels  of


office
   dept
      dept
         office
            office
                dept
                     .........


then the connect by hierarchical query suggested by slightwv would be appropriate
0
 
dgrafxAuthor Commented:
in this case i just have 2 levels
office
      dept
office
      dept

I'm not sure i'm posting the right info - i was experimenting.
I really just need to do this

office (listed just once - now there are several rows of one office)
        dept
        dept
office
         dept
         etc

btw - this is all from one table
I'm doing a simple report to write an excel sheet and cant get it to work

thanks ...

0
 
slightwv (䄆 Netminder) Commented:
Can you post some sample data?
0
 
sdstuberCommented:
alternate (sorry about the attachment, for whatever reason I get an error no trying to post directly)
ee.txt
0
 
sdstuberCommented:
you'll have to do some sort of union in order to "double up" your data in order to create a hierarchy where offices are above  departments.

you "could" still do it with connect by, it'd look something like this...  but I'd go with one of the above first.


SELECT     LPAD('-', (LEVEL - 1) * 3, '-') || officename
      FROM (SELECT officeid, officename, 0 deptid FROM yourtable
            UNION
            SELECT officeid, deptname, deptid FROM yourtable)
CONNECT BY officeid = PRIOR officeid AND deptid > 0 AND PRIOR deptid = 0
START WITH deptid = 0
0
 
dgrafxAuthor Commented:
thanks guys - i will visit this tomorrow - i am out of the office today.

thanks
0
 
dgrafxAuthor Commented:
This one works as I needed - thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.