Archives of the TeradataForum

Message Posted: Sun, 08 Nov 2004 @ 01:46:50 GMT

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

Parijat:

The first thing you need to do is take the FastExport commands out of a BTEQ script. There is no logtable in BTEQ. The second thing is that if you are exporting the commands to run, you do not want them to be data. Instead you should use REPORT and get rid of the titles.

Try something more like this:

     .RUN FILE LOGON1;
     .EXPORT DDNAME=DATAOUT ;   /* you need JCL with DISP=NEW, DCB, SPACE, etc to create a file */
     .IMPORT DATA DDNAME=INPUT1;
     USING (curryyy char(6))
     SELECT 'INSERT INTO xx.aggregate_table_'||
         cast((add_months(calendar_date, -1)
         (format 'yymm') (char(4))) as CHAR(4))
     from Sys_Calendar.CALENDAR where
         calendar_date (format 'yyyymmdd')(char(8))=:curryyy||'01';
     SELECT 'select * from xx.pp' ;
     .EXPORT RESET ;
     .RUN DDNAME=DATAIN;  /*  this DD statement reads the file created above as DATAOUT   */
     .LOGOFF ;

You need to get a better understanding of BTEQ commands vs BTEQ. It appears that you need some help with JCL too if you are going to be working from the mainframe.

Additionally, since you are only substituting a parameter for a data comparison, it would be better to use a macro for this instead of regenerating the same SQL over and over again. This really is a case of over-kill.

Dynamic SQL is best when you need to substitute a table name, a column name or something else from another table, as in the Data Dictionary. I believe a macro will simplify your life and more efficiently accomplish the above processing. Plus, with a macro you could execute it from Queryman/SQL Assistant or other environments.

Hope this helps get you on a better path.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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