Tuesday, September 9, 2008

Change a Table Without Reloading Report


Last month I wrote a posting about interesting things you can do in the loadscript with a Partial Reload. Here's another:

I had a large report that we needed as an example for a presentation but it was just too large. The original database it loaded from is no longer available, so I needed a way to make the report smaller without reloading it. It had one large shipment data table in it and I decided to change the the table to only include two shipping location codes which would make it much smaller.

See the picture above (click it for a better view). I inserted this code at the top of the loadscript:

/* Example loadscript fragment to adjust a table */
/* Put this code at the top of the loadscript and */
/* then run a Partial Reload. */
/* Remove this fragment after using it. */

//Rename the table we want to change
RENAME TABLE HIST TO HISTX;

//Rebuild table with a WHERE clause
HIST:
ADD NOCONCATENATE LOAD * RESIDENT HISTX
WHERE (LOC='001099') or (LOC='011098');

// Drop the original table
DROP TABLE HISTX;

//so that we don't fall into the regular loadscript
EXIT SCRIPT;


Then, I saved the report file under a different name (just in case) and ran a Partial Reload. The Partial Reload executed the commands at the top of the loadscript without removing all of the other data and made the report file a lot smaller and the memory or RAM requirement for the report smaller too. Unless you know that you'll never need to reload the report again you should remove the loadscript lines you added at the top.

Don't forget the ADD or the NOCONCATENATE keywords or you'll find that the table you wanted to rebuild is missing after you run the Partial Reload.

2 comments:

Rob Wunderlich said...

Clever idea Tim. Another technique that can be used to make a subset of data is the Reduce Data menu command.

Make the selections you want to filter the data and select File, Reduce Data, Keep Possible values from the menu. Then File, Save As, if you don't want to overlay your original copy.

xine78 said...

Thanks for this! I was struggling to figure out how to avoid reloading my existing table when adding a new table. I piggybacked off of this & it worked (keep existing AIR table and add new HOTEL table):

RENAME TABLE AIR TO AIR2;

AIR:
ADD NOCONCATENATE LOAD * RESIDENT AIR2;

DROP TABLE AIR2;


HOTEL:
REPLACE LOAD *;
SQL
Select * from table;