Creating View with Unions

Hi, sorry, it's a old question from Informix Users Group; I was on vacation. In my opinion it's a interesting problem. >From: amitkp@wipsys.soft.net (AMIT KUMAR PERVAJE) >Date: Tue, 9 Jul 1996 15:23:27 -4240 (GMT) >Subject: Cannot use UNIONS in creating View > >Hi, > >I am facing a problem in converting Oracle Code to Informix Code. I have a >a view which is defined as a union of many rows from the same table. >Basically the view transposes a row into about 5 rows. > >The SQL goes something like this >create view v_view as >select 1, col1, col2, col3 ... >from table_x >where col1 = something_1 >union >select 2, col1, col2, col3 ... >from table_x >where col1 = something_2 >union >select 3, col1, col2, col3 ... >from table_x >where col1 = something_3 > : > :> >Informix does not allow UNIONS to define viwes. > >The view is used in the front end for data display & in the backend for >Report generation. > >Can anyone help me out ?? > >Bye & Regards, >Amith Kumar Pervaje >Senior Systems Engineer >Wipro Systems Limited >EMail ID: amitkp@wipsys.soft.net I put forward following: CREATE PROCEDURE Tst( something INT ) RETURNING smallint; define result smallint; -- your Data_type & conditions define something_1 INT; define something_2 INT; define something_3 INT; LET something_1 = 10; LET something_2 = 20; LET something_3 = 30; IF something = something_1 THEN LET result = 1; ELIF something = something_2 THEN LET result = 2; ELIF something = something_3 THEN LET result = 3; ELSE LET result = 0; END IF RETURN result; END PROCEDURE; -- This SELECT is the equivalent -- to your SELECT * from v_view : SELECT Tst( col1 ) pseudo_union_num, col1, col2, col3 from table_x where Tst( col1 ) != 0 order by pseudo_union_num; Best regards, -- Julian Zalutsky, MIS. Brest, Republic of Belarus. E-mail: julian@martcom.belpak.brest.by