Re: Creating a view over identically structured tables

I had a similar problem. The following is from memory (unfortunately my own) so don't expect it to work as is. This is a 4GL solution: Create a new table (table_control) with columns for the table name (tr_tabname) and month ending date (period_enddate). --snip-- declare table_cursor cursor for select tr_tabname from table_control a where a.period_enddate between query_startdate andquery_enddate -- query_startdate and query_enddate are from user input foreach table_cursor into ret_tabname let string1 = " select COLUMN_LIST from ",ret_tabname, " where transaction_date between ", query_startdate," and ", " query_enddate" -- may have to put additional quotes around above variables - bad memory -- string1 must be a large char variable let string2 = string1 clipped let string2 = string2," union " -- string2 is huge char variable end foreach let string2_length = length(string2) let string2 = string2[1,string2_len - 7] -- this removes the last "union" prepare big_query from string2 execute big_query -- end snip -- Okay, it needs work but it should get you headed in the right direction. The best part about it is that it should allow an OL engine to perform the query in parallel. I don't know if it does, but it should. Sorry for any mistakes in the above, but you can debug it. It does work on SE. Hope it helps Tom Barnes wwhf15a@prodigy.com rwheeler@link.com.au (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 >