Archives of the TeradataForum

Message Posted: Thu, 20 Jul 2006 @ 20:29:24 GMT

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

<-- Anonymously Posted: Thursday, July 20, 2006 11:19 -->

Naomi,

Here's how I'd handle the problem that you posed about altering 70 macros at once.

First,
I'd write a query that writes the show macro statement for all 70 macros. It sounds like all your macros access the same view so I'd put that in the where statement. So it'd look something like:

     Select 'Show macro '||trim(databasename)||'.'||trim(tablename)||' ;'
     from dbc.tables
     where tablekind = 'm'
     and     requesttext like '%VIEW_TO_CHANGE%'
     and  databasename = 'database_where_macros_reside' /*this might not be
     relevant */
     order by 1

Execute and verify that your 70 macros (and nothing else) are captured.

Second,
Copy and paste the results of the previous query (i.e. the show macro commands) into Queryman. Go to File and select Export Results. Execute. You should be prompted for a file name where the results of the show macro commands will be stored. Enter a file name and wait for execution to complete. Unfortunately, the results of each show macro will be placed in it's own file (e.g. filename, filename2, filename3,...., filename70)

Third,
Concatenate these 70 files into one for easy editing. I ftp the files to our unix machine and use this command: cat filename* >newfile. Then ftp the newfile back to my PC and remove the 70 files (rm filename*). I'm sure there's probably an easy way to do this in DOS, but I'm not sure what it is.

Fourth,
open the "newfile" with any text editor. Get rid of the 'Request Text' comment that precedes each create or replace macro statement.

Do your editing, a mass change on the view you wish to alter, change 'create' to be 'replace', etc.

Copy and paste edited text into Queryman and execute.

That's it. Good luck.

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