Followup to Multiple Master/Detail Relationships in ACE (LONG)

Path: emory!wupost!zaphod.mps.ohio-state.edu!sample.eng.ohio-state.edu!purdue!yuma!ccncsu!lamar!sharif From: sharif@lamar.ColoState.EDU (Thomas Abdallah) Newsgroups: comp.databases.informix Message-ID: <19933@ccncsu.ColoState.EDU> Date: 12 Dec 91 20:21:00 GMT Sender: news@ccncsu.ColoState.EDU Organization: Colorado State University, Fort Collins, CO 80523 Many thanks for the help given in response to my original question about multiple master/detail relationships within an ACE report. I am posting the responses which I received for anyone else having problems with this. Thomas-Sharif Abdallah (Responses follow) -------------------------------------------------------------------------- To: sharif@lamar.ColoState.EDU (Thomas Abdallah) From: mercer@ux.acs.umn.edu (Steven E. Mercer) Thomas-Sharif Abdallah, In regards to your question in comp.databases.informix: I was bored so I whipped up this report for you. Try it and tell me if it does what you need. I believe that it solves the problem that you described. Please post this to comp.databases.informix as I am unable to post outside of my local system. Steve -------------------------------------------------------------------------- Steven E. Mercer "Write a wise saying and your name mercer@ux.acs.umn.edu will live forever." -Anonymous -------------------------------------------------------------------------- -----CUT HERE----- {------------------------------------------} { Report written by Steven E. Mercer } { mercer@ux.acs.umn.edu } { University of Minnesota, Minneapolis MN } { COPYRIGHT 1991, Steven E. Mercer } { Permission is hereby granted to use this } { report and the ideas contained within it } { for any purpose, under the following two } { conditions: } { (1) The user assumes all risk. } { (2) My name and this copyright notice } { must be included in the source code. } {------------------------------------------} { Here is a report that I belive solves your problem of printing } { multiple master/detail relationships in a single ACE report. } { This report runs correctly on my system: 4.00 Standard SE engine } { with the ISQL front-end on a PYRAMID machine. This method should } { work with all standard systems running version 2.10 and later, but } { I cannot guarantee this. USE THIS REPORT AT YOUR OWN RISK! } { The method used in this report REQUIRES that the formid column } { must be unique within the formdef table. That is, no two rows in } { the formdef table may have the same formid value. } database test end { replace test with your database name } define variable skunkcount integer { determines first line of skunkmark data } variable recordcount integer { determines first line of recorddef data } end { For simplicity's sake, This report assumes that all columns } { are defined to be of type CHARACTER. } { SELECT statements: } { You need to adjust the null data constants " " to reflect } { the data types used for your columns. } { PRINT statements: } { You need to change the CLIPPED format into a format that is } { compatable with the column data types. } { The following UNION SELECT statement gets all information from } { all tables, puts it into a single data structure, and orders it } { in a way that enables us to extract the desired data from the } { proper table when we need it. } { get the data from the formdef table } select formid, { the key attribute } "0" datatype, { used to tell what table this data is from } ftype, { form type } fname, { form name } appnum, { application number } " " sheetnum, { null - used for skunkmark data } " " sidenum, { null - used for skunkmark data } " " rownum, { null - used for skunkmark data } " " colnum, { null - used for skunkmark data } " " question, { null - used for recorddef data } " " qtype, { null - used for recorddef data } " " qlength, { null - used for recorddef data } " " qstart, { null - used for recorddef data } " " qend { null - used for recorddef data } from formdef union all { get the data from the skunkmark table } select formid, { the key attribute } "1" datatype, { used to tell what table this data is from } " " ftype, { null - used for formdef data } " " fname, { null - used for formdef data } " " appnum, { null - used for formdef data } sheetnum, { sheet number } sidenum, { side number } rownum, { row number } colnum, { column number } " " question, { null - used for recorddef data } " " qtype, { null - used for recorddef data } " " qlength, { null - used for recorddef data } " " qstart, { null - used for recorddef data } " " qend { null - used for recorddef data } from skunkmark union all { get the data from the recorddef table } select formid, { the key attribute } "2" datatype, { used to tell what table this data is from } " " ftype, { null - used for formdef data } " " fname, { null - used for formdef data } " " appnum, { null - used for formdef data } " " sheetnum, { null - used for skunkmark data } " " sidenum, { null - used for skunkmark data } " " rownum, { null - used for skunkmark data } " " colnum, { null - used for skunkmark data } question, { question } qtype, { question type } qlength, { question length } qstart, { question start } qend { question end } from recorddef order by 1, 2, 6, 7, 8, 9, 10 { Notes about the ORDER BY statement: } { You must use column numbers instead of names for UNION select. } { 1 groups all records with same formid } {