Re: Split rows in a table to multiple tables.

Path: emory!sol.ctr.columbia.edu!spool.mu.edu!uunet!gossip.pyramid.com!pyramid!infmx!news From: cortesi@informix.com (David Cortesi) Newsgroups: comp.databases.informix Message-ID: <1992Jan13.174434.12347@informix.com> Date: 13 Jan 92 17:44:34 GMT References: <438@rhlab.UUCP> Sender: news@informix.com (Usenet News) Reply-To: cortesi@informix.com Organization: Informix Software, Inc. In article <438@rhlab.UUCP> kuhn@rhlab.UUCP (Michael Kuhn) writes: > I have an SQL problem that I can not figure out. I have reduced the > tables and the problem to a simplier form. > > [extremely clear & detailed presentation omitted to save space] > [the basic problem is to partition a table, selecting groups of rows > into different temp tables based on the value of column "fld"] You don't say but I assume these conditions on the solution: * noniterative, that is, you don't want to deal with cursors or "for i = 1 to n" type solutions this implies the number of partitions is fixed, i.e. you always want 3, or 5, or whatever number of temp tables. * every rows of table "test" is to be selected to some temp table * no row is to be selected to two or more temp tables Note that your use of min(fld) and max(fld) to select two partitions meets the above conditions only when there are precisely two unique values in the column -- if there is only one unique value in the column all rows are selected to both tables; and if there are more three or more some will of course be left out. The basic requirement is to choose how many partitions you want, and then to devise expressions that yield that many partitions of the contents of column "fld". It seems to me the obvious and most flexible way is to set up a table that gives the partitioning values. Say you want to split it in four partitions: tmpAF, tmpGL, tmpMR, and tmpSZ. Create a permanent table "parts": pno first last 1 "A" "F" 2 "G" "L" 3 "M" "R" 4 "S" "Z" select test.* from test, parts where test.fld >= parts.first and test.fld <= parts.second and parts.pno = 1 into temp tmpAF And so forth for the other three partitions. Is this too easy? It presumes that the range and distribution of values in column fld is known. (Note the speed of operation would be drastically improved by an index on column test.fld)