All Forums Database
alihammad 16 posts Joined 03/11
09 Jun 2016
Get database hierarchy to create similar directory structure on filesystem

I'm struggling with a query to fetch entire database hierarchy and calculating path of each node from root. I need to create same directory structure on the file system whereby each directory represents a database.
 
Example
 
RootDB
--Level1-Child1
----Level2-Child1
----Level2-Child2
------Level3-Child1
--Level1-Child2
--Level1-Child3
 
 
So far I haven't made much progress
 

SEL c.child, c.parent, TRIM(c.parent) || '/' || TRIM(c.child) FROM (
SEL a.Child, b.Parent FROM (
SEL child
FROM 
dbc.ChildrenV  
WHERE 
Parent = 'gcfr_main'

)a, dbc.ChildrenV b
WHERE a.child = b.Child  
AND Parent <> 'dbc'
--Parent <> 'gcfr_main'
--ORDER BY a.child
) c
WHERE c.parent <> 'gcfr_main'
ORDER BY c.parent

 

CarlosAL 512 posts Joined 04/08
09 Jun 2016

Hi.

WITH RECURSIVE JER (LEVEL, DATABASENAME, OWNERNAME, ROUTE)
AS
(
SELECT 1 LEVEL,
       DATABASENAME,
       OWNERNAME,
       CAST('/'||TRIM(DATABASENAME) AS VARCHAR(128)) ROUTE
  FROM DBC.DATABASES
 WHERE DATABASENAME='DBC'
UNION ALL
SELECT JER.LEVEL + 1,
       D.DATABASENAME,
       D.OWNERNAME,
       JER.ROUTE || '/' || TRIM(D.DATABASENAME) 
  FROM DBC.DATABASES D,
       JER
 WHERE d.DATABASENAME<>'DBC'
   AND d.OWNERNAME = JER.DATABASENAME
)
SELECT LEVEL, 
       DATABASENAME, 
       OWNERNAME, 
       ROUTE
  FROM JER
 ORDER BY 4
;

HTH.
Cheers.
Carlos.

alihammad 16 posts Joined 03/11
09 Jun 2016

@CarlosAL Thanks mate. Looks like that's exactly what I want. Let me decipher it as I'm not used to recursion.
 
Cheers

You must sign in to leave a comment.