Re: Problem: Reference and print records of the same table
On 29 Sep 1995 10:25:39 GMT, you wrote:
>Hi everybody,
>We got INF4.1 on a Siemens Hardware with MIPS CPU.
>We have a database-program to administrate our library
>holdings. We have downloaded them from that program
>an loaded them to a informix-database.
>As the download-file is a plain text-file, we have
>loaded into one table.
>Each record has a field containing his (unique) accesion
>number, and if present, one OR MORE fields with accession
>numbers to parent entries.
>That means, a certain piece, which is a book on its own,
>is a part of a title, and the title is a part of a row.
>What I want is an ace-script, which selects the piece, and
>if present, the parent entries.
>Now here is my question:
>How can I print both (or more) titles (for example),
>of the piece and the row, if both fields are named TI,
>because they are both in the same table.
>Any help is welcome - Ruediger
>P.S.: Yes, master-detail would be nicer, but it is much
> more complicated for the filter from plain download
> file to informix master-detail database.
The solution is very simple
I assume the table layout to be:
e.g.
create table tablename (
accesion_number char(10),
accesion_text char(256),
accesion_1 char(10),
accesion_2 char(10),
accesion_3 char(10),
accesion_4 char(10),
accesion_5 char(10),....and so on...
)
and accesion_? points back to accesion_number to link the records.
In the Database Selection of your ACE Report.
select "0" rec_type, {Rec Type 0 = Master Accesion}
accesion_number accesion_master, {Renamed to accesion_master}
accesion_number accesion_child, {Renamed to accesion_child}
accesion_text
from tablename
where accesion_number = "????"
UNION
select "1" rec_type, {Rec Type 1 = 1st Child Accesion}
a.accesion_number accesion_master, {Renamed to accesion_master}
b.accesion_number accesion_child, {Renamed to accesion_child}
b.accesion_text
from tablename a, tablename b
where a.accesion_number = "????"
and b.accesion_number = a.accesion_1
UNION
select "2" rec_type, {Rec Type 2 = 2nd Child Accesion}
a.accesion_number accesion_master, {Renamed to accesion_master}
b.accesion_number accesion_child, {Renamed to accesion_child}
b.accesion_text
from tablename a, tablename b
where a.accesion_number = "????"
and b.accesion_number = a.accesion_2
UNION.....
and so on for each possible occurance....
*** NOTE: ACE report writer does have a limit on the number of UNION's
that can be used. ***
*** and varies from version to version ***
The rec_type is only used to keep the records in the same sequence as
they are stored in the record.
On last select put your order by to achieve the required sequence
This also solves the naming problems you had in the OUTPUT section
ps
It may be wise to test in SQL first then insert it into the ACE
Report.....
Best of Luck !!
---------------------------------------------------------------------
Cliff Warrington
KTL Partnership
Tel : +44 (0)161 763 1286
Fax : +44 (0)161 763 1180
Email : cliff@ktl.u-net.com
---------------------------------------------------------------------