Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Dynamic SQL in Tera data
 
From:   Frank O Connor

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016