Archives of the TeradataForum

Message Posted: Wed, 27 Jul 2011 @ 15:06:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Nathan_S_Smith wrote:

You need to add a "materialized path" for sorting.

This is a lab from my trainings:

     CREATE VOLATILE TABLE Databases AS
       (
         SELECT
           DatabaseName,
           OwnerName,
           PermSpace
         FROM dbc.Databases
       ) WITH DATA
     UNIQUE PRIMARY INDEX (DatabaseName);

     WITH RECURSIVE cte (DatabaseName, Path, Level) AS
       (
         SELECT TRIM(DatabaseName)
                ,DatabaseName(VARCHAR(600))
                ,0 (BYTEINT)
         FROM   Databases d
         WHERE  DatabaseName = 'dbc'

         UNION ALL

         SELECT TRIM(d.DatabaseName)
                ,cte.Path || '.' || TRIM(d.DatabaseName)
                ,Level + 1
         FROM   Databases d
                ,cte
         WHERE  d.OwnerName = cte.DatabaseName
         AND    d.DatabaseName <> d.OwnerName
         AND    Level < 20
       )
     SELECT level
             ,SUBSTRING(CAST('' AS CHAR(60)) FROM 1 FOR LEVEL * 2)  || DatabaseName AS Hierarchy
             ,DatabaseName
             ,Path
     FROM   cte
     ORDER BY path;

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>