• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 626
  • Last Modified:

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
0
dgrafx
Asked:
dgrafx
  • 5
  • 3
  • 2
1 Solution
 
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
 
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now