create view as table unions - Syntax Errors?

I have got a number of tables, item_d0, item_d1, item_d2, ... They are of the same schema definition. Instead of merging them into one giant table, I tried to CREATE VIEW item_all AS SELECT * FROM item_d0 UNION SELECT * FROM item_d1 dbaccess complains a syntax error just before the keyword UNION. I tried some variations like CREATE VIEW item_all (item_id) as SELECT item_id FROM item_d0 UNION SELECT item_id FROM item_d1 but I still don't go any where. I would appreciate if anyone can point me to any blind spots. Thanks in advance. "Adrian"

Thanks for the replies. So, Informix 7.2x does not support views with unions in SELECT statement clause. Does anyone have ideas how to achieve something equivalent. Merging the tables together to a physical table is the last thing I want to do but I wish to learn the alternatives. Thanks in advance!! "A"

Adrian Cho wrote: > > I have got a number of tables, > > item_d0, item_d1, item_d2, ... > > They are of the same schema definition. > > Instead of merging them into one giant table, I tried to > > CREATE VIEW item_all AS > SELECT * FROM item_d0 > UNION > SELECT * FROM item_d1> > dbaccess complains a syntax error just before the keyword UNION. [SNIP] IDS 7.2x does not support view containing UNIONs. IDS version 7.3x does. Alternatively you could ALTER FRAGMENT and attach the several tables as fragments of the same table. I assume there is a scheme to which table a row is places so the new fragmentation scheme can match that logic and simplify your load program/procedures. Art S. Kagel