Split rows in a table to multiple tables.
Path: emory!swrinde!cs.utexas.edu!uunet!rhlab!kuhn
From: kuhn@rhlab.UUCP (Michael Kuhn)
Newsgroups: comp.databases.informix
Keywords: SQL, Select
Message-ID: <438@rhlab.UUCP>
Date: 10 Jan 92 21:36:53 GMT
Organization: Baltimore Rh Laboratory, Inc., Maryland
I have an SQL problem that I can not figure out. I have reduced the
tables and the problem to a simplier form.
Given the following:
create table test (fld char(3), pos integer, id integer, val integer);
insert into test values ("X", 1, 110, 10);
insert into test values ("X", 2, 101, 11);
insert into test values ("X", 3, 102, 10);
insert into test values ("X", 4, 103, 12);
insert into test values ("X", 5, 104, 12);
insert into test values ("X", 6, 105, 12);
insert into test values ("Y", 1, 100, 10);
insert into test values ("Y", 2, 101, 10);
insert into test values ("Y", 3, 102, 12);
insert into test values ("Y", 4, 103, 12);
insert into test values ("Y", 5, 104, 10);
insert into test values ("Y", 6, 105, 11);
insert into test values ("Y", 7, 105, 11);
select unique fld from test into temp uqf;select max(fld) fld from uqf into temp mxf;
select min(fld) fld from uqf into temp mnf;
select test.* from test, mxf
where test.fld = mxf.fld into temp mxt;
select test.* from test, mnf
where test.fld = mnf.fld into temp mnt;
select mxt.fld, mxt.pos from mxt
where mxt.pos not in (select pos from mnt);
select mnt.fld, mnt.pos from mnt
where mnt.pos not in (select pos from mxt);
mxf - contains "Y"
mnf - contains "X"
mxt - contains test where fld = "Y"
mnt - contains test where fld = "X"
This works for a two-way split.
ANYBODY HAVE ANY IDEAS ON HOW TO SPLIT OUT TO MORE THAN 2 TABLES?
Without specify a where clause with the distinct values for "fld".
--
Michael J. Kuhn Consultant phone:410-254-7060
Email: rhlab!kuhn@uunet.uu.net or uunet!rhlab!kuhn
c/o Baltimore Rh Typing Laboratory, Inc. phone:410-225-9595