?
Solved

showing a sub hierachy in a main hierarchy query using connect by prior

Posted on 2007-03-25
1
Medium Priority
?
420 Views
Last Modified: 2013-12-19
folks

here is a hierarchy in oracle

SELECT LPAD(' ',2*(LEVEL-1)) || TO_CHAR(location) s
  FROM location
  START WITH location ='12000'
  CONNECT BY PRIOR location = parent;

AS  you see the table is called location,my asset table also has its own hierarchy,but in affect has a relation to the location table location.location =asset.location,below is its own hierarchy
 
  SELECT LPAD(' ',2*(LEVEL-1)) || TO_CHAR(ASSETNUM) s
  FROM asset
  START WITH assetnum ='AGV314'
  CONNECT BY PRIOR assetnum = parent;

 how do i join both selects?to show all locations as all its assets in one hierarchical query?

all help will do
0
Comment
Question by:rutgermons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 15

Accepted Solution

by:
OMC2000 earned 2000 total points
ID: 18791100
SELECT location, a.s,  b.s FROM
(SELECT location, LPAD(' ',2*(LEVEL-1)) || TO_CHAR(location) s
  FROM location
  START WITH location ='12000'
  CONNECT BY PRIOR location = parent) a
LEFT OUTER JOIN
(SELECT location,  LPAD(' ',2*(LEVEL-1)) || TO_CHAR(ASSETNUM) s
  FROM asset
  START WITH assetnum ='AGV314'
  CONNECT BY PRIOR assetnum = parent) b
ON a.location = b.location

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

777 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