Archives of the TeradataForum

Message Posted: Fri, 05 Sep 2003 @ 08:17:04 GMT

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

Figge, Jason wrote:

I know NCR added a warning to the BTEQ manual some time ago:

"If a run file contains a RUN command to run itself, BTEQ generates an out-of-memory message after several iterations. Always avoid such a loop."

But did you really ever had that kind of problem? "Several" seems to be a very large number ;-)

I usually show a recursive approach, adding the warning from the manual...

Dieter

/*** file recursion1.btq ***/
.logon ...;

CREATE VOLATILE TABLE vt_hierarchy, NO FALLBACK, NO LOG
(DatabaseName char(30) not null primary key,
  OwnerName    char(30),
  Level        integer,
  rnk          integer,
  Sort         varchar(1024)
) ON COMMIT PRESERVE ROWS
;

INSERT INTO vt_hierarchy
   SELECT DatabaseName, OwnerName, 0, RANK(DatabaseName ASC), ''
   FROM dbc.Databases
   GROUP BY OwnerName
;

.RUN FILE = recursion2.btq;

/*** file recursion 2.btq ***/
UPDATE vt_hierarchy
FROM
  (SELECT
     child.DatabaseName
    ,parent.Level
    ,parent.Sort || (child.rnk (format '999')) AS NewSort
   FROM
       vt_hierarchy parent
     JOIN
       vt_hierarchy child
     ON parent.DatabaseName = child.OwnerName
   WHERE parent.Level = (SEL MAX(Level) FROM vt_hierarchy)
   AND child.DatabaseName <> 'dbc'
  ) AS tmp
SET Level = tmp.Level + 1
    ,Sort = tmp.NewSort
WHERE vt_hierarchy.DatabaseName = tmp.DatabaseName
;

.IF ACTIVITYCOUNT > 0 THEN .RUN FILE = Recursion2.btq;

SELECT SUBSTRING('              ' FROM 1 FOR Level * 2) || DatabaseName
AS Hierarchy
FROM vt_hierarchy ORDER BY Sort
;

DROP TABLE vt_hierarchy;

/*** As BTEQ does not return to the last file, everything following the .RUN is done only once  ***/

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