Re: Creating a view over identically structured tables

Rodney Wheeler wrote: > > Each month processed transactions are archived to a new table with the > year and month forming part of the table name eg oldtrx9511, > oldtrx9512, oldtrx9601 etc. The tables all have identical columns. > This archiving is done to speed up the handling of the unprocessed > transactions. > > For historical reporting purposes I would like to create a view over > all the archive tables (oldtrx*) so they can be treated as one table - > how can I do this? > > We are currently using HP-UX 9.04 and Informix SE SQL 4.13 UD1. > > Any help would be greatly appreciated (and yes we will be upgrading to > a newer version of Informix sometime soon)! > > Peter Louis > Link Telecommunications As you've got ISQL I don't think there's a way of doing it in one step and I assume you want a process you can run to get the whole set of data WITHOUT HARDCODING THE TABLE NAMES. You could set up a small ace report to build you a "CREATE VIEW" SQL statement. Use OUTPUT TO "CREATEVIEW.SQL" and base it on a: SELECT TABNAME FROM SYSTABLES WHERE TABNAME MATCHES "oldtrx*" and build something that writes the following: CREATE VIEW alltables AS SELECT * FROM oldtrx0195 UNION SELECT * FROM oldtrx0295 UNION etc... Then you can stick a DROP VIEW at the top and just run it. You could then set up a UNIX script that did: sacego createview.arc isql dbname createview.sql Note: I haven't tested this syntax but I'm trying to get across the general idea. Here's wishing you luck! -- Gary Rooksby aa46@dial.pipex.com