Archives of the TeradataForum

Message Posted: Mon, 05 Jan 2004 @ 10:09:34 GMT

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

Hi

You could try the following macro. You will need to use queryman or bteq to export the results as suggested by Mike Dempsey.

Cheers

Frank

replace macro userid.nullcount
(databasename char(8) not null, tablename char(30) not null)
as
(
select
        sqltext
from
(select

                0 (smallint) /* used to order sql statements */
                , 'select' (char(200))
from
        dbc.next /* a one row table */
union
select
        columnid
        , case when c.columnid = mincol
                                then 'sum(case when '
                                                || columnname
                                                || ' is null then 1 else 0 end) (real) (named '
                                                || trim(columnname)
                                                || ')'
                else
                                        ', sum(case when '
                                        || columnname
                                        || ' is null then 1 else 0 end) (real) (named '
                                        || trim(columnname)
                                        || ')'
        end
from
/* Derived table used to determine if it is the first column.   */
/* This determines whether a comma is need in the sum statement */
        (select min(columnid), max(columnid)
                from
                                dbc.columns
                where
                                tablename = :tablename
                and     databasename = :databasename)
                cols (mincol, maxcol)
inner join
        dbc.columns c
on
        c.columnid between mincol and maxcol
        where c.databasename = :databasename
        and      c.tablename = :tablename
union
        select
        32000
        , ' from ' || :databasename ||'.' || :tablename
from dbc.next
)
temp (columnid, sqltext)
order by columnid;);

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