Hello, I'm hoping that some guru here can help me with a little taks I
have.
I have a pervasive table called dwgs which has a structure like:

dwgno
equip
equip_a
equip_b
equip_c
equip_d
equip_e
equip_f
equip_g
equip_h
equip_i
equip_j
equip_k
equip_l
equip_m

I cannot change this structure. Now for an particular dwgno it may
have 1 or more equip.. fields filled in. I need to generate a report
in equip order. Historically I have done this through a long code
process which looks at each record in my dwgs table and creates a
record in an equip table for each filled equip field. ie if a dwgno
has 5 filled fields I will end up with 5 records in my equip table.
The structure of my equip table is

equip
dwgno

Is there a clever way of doing this ? I was thinking about some way of
creating an equip table SQL with 14 records per dwgno relating to each
equip field and then deleting the records with blank equip after.

Any other ideas ?
Many thanks

Would have been helpful to have your database version to be sure this will
work. This SHOULD work on PSQLV8 and PSQL2000i. Some shortcuts have been
taken in the field lists -- you will need to do the real work of typing
the fields. Also, I did not check for syntax, so it may need some minor
adjustment.

CREATE PROCEDURE FillEquipList WITH DEFAULT HANDLER
AS BEGIN
DECLARE :dwgno CHAR(20);
DECLARE :e1 CHAR(20);
DECLARE :e2 CHAR(20);
DECLARE :e3 CHAR(20);
DECLARE :e4 CHAR(20);
DECLARE :e5 CHAR(20);
DECLARE :e6 CHAR(20);
DECLARE :e7 CHAR(20);
DECLARE :e8 CHAR(20);
DECLARE :e9 CHAR(20);
DECLARE :e10 CHAR(20);
DECLARE :e11 CHAR(20);
DECLARE :e12 CHAR(20);
DECLARE :e13 CHAR(20);
DECLARE :e14 CHAR(20);
DECLARE curs CURSOR FOR select dwgno, equip, [rest of fields] from dwgs;
OPEN curs;
FETCH NEXT FROM curs INTO :dwgno, RTRIM(:e1), RTRIM(:e2), [rest of
fields];
WHILE(SQLSTATE = '00000') DO
IF :e1 <> '' THEN INSERT INTO EquipmentList VALUES (:dwgno, :e1); END
IF;
IF :e2 <> '' THEN INSERT INTO EquipmentList VALUES (:dwgno, :e2); END
IF;
[repeat for each of the 15 fields]
FETCH NEXT FROM curs INTO :dwgno, RTRIM(:e1), RTRIM(:e2), [rest of
fields];
END WHILE;
CLOSE curs;
END;
#

To use this procedure, you first create the file, then call the proc, and
you should have your data. This looks like:

CREATE TABLE EquipmentList
( dwgno CHAR(20), equip CHAR(20) ) #
CREATE INDEX Index0 AS EquipmentList (dwgno, equip) #
CREATE INDEX Index1 AS EquipmentList (equip, dwgno) #
CALL FillEquipList() #
SELECT * From EquipmentList ORDER BY equip #

Obviously, you'll need to adjust field types, lengths, and other
information. You can eliminate duplicates with either additional IF
statements or by making the index UNIQUE. Feel free to embellish it in
other ways, too.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: June, 2004: See our web site for details!


spike wrote: