title: How to execute dynamic SQL in Teradata
body: <p>Is there any way to submit dynamically generated SQL to Teradata?  I've written a query that will create the code to denormalize a table.  Right now, I am pulling the code down to my client (SAS) and resubmitting it in a second step.  I am not familiar with either Teradata macros or procedures; would something like that work?</p>
  
  <p>To illustrate, I have a table defined like this:</p>
  
  <pre><code>create multiset table MYTABLE
      (  RECID  integer generated always as identity
                ( start with 1
                 increment by 1
                 minvalue -2147483647
                 maxvalue 2147483647
                 no cycle )
       , SNAP_DATE  date format 'YYYY/MM/DD'
       , EMAIL_DATE date format 'YYYY/MM/DD'
       , FREQ integer
      )
  unique primary index ( RECID  )
  </code></pre>
  
  <p>The table is populated every day (<code>SNAP_DATE</code>) and is used to monitor changes to an email_date in another table.  The following query returns the code that I can run to create my denormalized view:</p>
  
  <pre><code>select RUN_THIS
  from (
      select RUN_THIS, RN
      from (
          select 'select EMAIL_DATE ' (varchar(100)) as RUN_THIS
                , 0 (int) as RN
            ) x
  
      union all
      select ', sum( case when SNAP_DATE = date '''
               || (SNAP_DATE (format 'yyyy-mm-dd') (char(10)) )
               || ''' then FREQ else 0 end ) as D'
               || (SNAP_DATE (format 'yyyymmdd') (char(8)) ) as RUN_THIS
            , row_number() over ( partition by 1 order by SNAP_DATE ) as RN
      from ( select distinct SNAP_DATE 
             from MYTABLE 
             where SNAP_DATE &gt; current_date - 30) t1
  
      union all
      select RUN_THIS, RN
      from (
          select 'from MYTABLE group by 1 order by 1;' as RUN_THIS
                  , 10000 as RN
            ) y
      ) t
  order by RN
  </code></pre>
  
  <p>I export the result of the above query to a file on my client, then turn around and submit that file back to Teradata.  I'm hoping there is some way to store this complete definition in some Teradata object so it can be executed directly.</p>