|
|
Archives of the TeradataForum
Message Posted: Wed, 27 Jul 2011 @ 18:31:10 GMT
Subj: | | Re: Indented Bill of Material Query? |
|
From: | | John_Wight |
Here is another version of the Teradata DD/DS structure to make available to developers, etc. :
REPLACE RECURSIVE VIEW dbadmin.DBC_Hierarchy
(
DatabasePath
,DatabaseName
,LevelNbr
,ExtendedDatabaseName
,DatabaseType
)
AS
(
SELECT
CAST(TRIM(DatabaseName) AS VARCHAR(512)) AS DatabasePath
,DatabaseNameI AS DatabaseName
,CAST(0 AS INTEGER) AS LevelNbr
,CAST(TRIM(DatabaseName) AS VARCHAR(50)) AS ExtendedDatabaseName
,RowType AS DatabaseType
FROM
DBC.DBase
WHERE
DatabaseID = OwnerID
UNION ALL
SELECT
DS.DatabasePath
|| '.'
|| TRIM(DB.DatabaseName)
,DB.DatabaseNameI
,DS.LevelNbr + 1
,SUBSTR('....................', 1, DS.LevelNbr)
|| TRIM(DB.DatabaseName)
,DB.RowType
FROM
DBC_Hierarchy DS
INNER JOIN
DBC.DBASE DB
ON DS.DatabaseName = DB.OwnerName
AND
DatabaseID <> OwnerID
);
JK
| |