Re: Views and multiple tables

>From: harry@boi.hp.com () >Subject: Views and multiple tables >Date: Mon, 27 Sep 1993 21:34:41 GMT >X-Informix-List-Id: <news.4433> > >I have an environment where I want to have current information >stored in one table ( e.g. orders ) and historical information >stored in another ( hist_orders ). The tables are identical in >structure. > >Can I create a view that will allow me to give users access to >both tables at the same time ( i.e. union ). The Informix manuals >say I can do this ( i.e. a select statement that performs the >equivalent of a union ), but I am not sure how or if this is >possible. You can write SELECT * FROM orders UNION SELECT * FROM hist_orders to obtain the data you require. In general, you can write: CREATE VIEW ViewName AS SELECT ... Unfortunately, the manuals also say (Informix Guide to SQL Ref Manual Dec 1991, but the same story applies in previous versions): CREATE VIEW ... The SELECT statement ... cannot have an ORDER BY clause, INTO TEMP clause or UNION operator. The ANSI standard also that a view cannot use a UNION. It is, however, a common extension to allow them -- however, as far as I am aware, Informix does not provide this common extension. (It is conceivable that 5.02 OnLine or above does provide this -- I haven't checked -- but 5.01.UC2 doesn't accept the syntax). Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>