Get database hierarchy to create similar directory structure on filesystem

Database
Enthusiast

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
Tags (2)
2 REPLIES
Junior Supporter

Re: Get database hierarchy to create similar directory structure on filesystem

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.

Enthusiast

Re: Get database hierarchy to create similar directory structure on filesystem

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

Cheers