Archives of the TeradataForum

Message Posted: Thu, 21 Sep 2006 @ 14:48:28 GMT

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

Anomy.Anom wrote:

Better use a cross join.

Dieter

     /**
     How to normalize repeating groups
     **/

     CREATE TABLE denorm AS(
     SELECT
       databasename
       ,SUM(CASE WHEN Tablekind = 'T' THEN 1 ELSE 0 END) AS "T"
       ,SUM(CASE WHEN Tablekind = 'V' THEN 1 ELSE 0 END) AS "V"
       ,SUM(CASE WHEN Tablekind = 'M' THEN 1 ELSE 0 END) AS "M"
       ,SUM(CASE WHEN Tablekind = 'P' THEN 1 ELSE 0 END) AS "P"
       ,SUM(CASE WHEN Tablekind = 'G' THEN 1 ELSE 0 END) AS "G"
       ,SUM(CASE WHEN Tablekind = 'I' THEN 1 ELSE 0 END) AS "I"
       ,SUM(CASE WHEN Tablekind = 'J' THEN 1 ELSE 0 END) AS "J"
     FROM dbc.tables
     GROUP BY 1
     ) WITH DATA UNIQUE PRIMARY INDEX(databasename);


     /**
     Multiple UNIONs
     **/

     SELECT Databasename, 'Table', t FROM denorm
     WHERE t > 0
     UNION ALL
     SELECT Databasename, 'View', v FROM denorm
     WHERE v > 0
     UNION ALL
     SELECT Databasename, 'Macro', m FROM denorm
     WHERE m > 0
     UNION ALL
     SELECT Databasename, 'Trigger', g FROM denorm
     WHERE g > 0
     UNION ALL
     SELECT Databasename, 'Procedure', p FROM denorm
     WHERE p > 0
     UNION ALL
     SELECT Databasename, 'Join Index', i FROM denorm
     WHERE i > 0
     UNION ALL
     SELECT Databasename, 'Journal', j FROM denorm
     WHERE j > 0
     ORDER BY 1;


     /**
       CROSS-JOINs are (usually) more efficient,
       but there's need for a helper table.
       This might be generic (integers from 1 to x)
     **/

     CREATE TABLE tablekinds(TableKind CHAR, description VARCHAR(20));

     INSERT INTO tablekinds('T', 'Table');
     INSERT INTO tablekinds('V', 'View');
     INSERT INTO tablekinds('M', 'Macro');
     INSERT INTO tablekinds('G', 'Trigger');
     INSERT INTO tablekinds('J', 'Journal');
     INSERT INTO tablekinds('I', 'Join Index');
     INSERT INTO tablekinds('P', 'Procedure');

     SELECT
       d.DatabaseName,
       t.Description,
       CASE t.TableKind
         WHEN 'T' THEN d.T
         WHEN 'V' THEN d.v
         WHEN 'M' THEN d.m
         WHEN 'G' THEN d.g
         WHEN 'P' THEN d.p
         WHEN 'I' THEN d.i
         WHEN 'J' THEN d.j
       END AS cnt
     FROM denorm d CROSS JOIN tablekinds t
     WHERE cnt > 0;

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