Re: ACE Report Question
Chris Hushour writes:
->
->Thanks for the responses, but I guess I forgot to add the Phone Number
->to the output. What if the output looks like this:
->
-> Name: Bob ID #: 111111
->
-> Books: 1, 2, 3
->
-> Phone: 555-1212
->
->
-> Name: John ID #: 222222
->
-> Books: 5
->
-> Phone: 333-1212
->
->
-> Name: Jack ID #: 333333
->
-> Books: 4, 6
->
-> Phone: 111-1212
-> ...
->: I'm attempting to write an ACE report that uses multiple tables. Each page
->: of the report contains 1 row from each of the tables except for one of the
->: tables which contains multiple rows. I will visualize:
->
->: Table 1: ID# | Name | Phone |
->: ------+------+----------+
->: 111111| Bob | 555-1212 |
->: 222222| John | 333-1212 |
->: 333333| Jack | 111-1212 |
->
->: Table 2:
->
->: ID# | Book # |
->: ------+--------+
->: 111111| 1 |
->: 111111| 2 |
->: 111111| 3 |
->: 222222| 5 |
->: 333333| 4 |
->: 333333| 6 |
->
->: Any thoughts or suggestions?
Chris,
Actually, you can do quite a lot with ACE. Try something like:
database <whatever> end
define
variable first_book smallint
end
select id_number, name, phone, book
from t1, outer t2 { Use outer only if you want people with no books }
where t1.id_number = t2.id_number
order by id_number, book_number
end
format
before group of id_number
print column 1, "Name: ", name,
column 20, "ID #: ", id_number
skip 1 line
print column 1, "Books: "
let first book = true
on every row
if first_book is true then
print ", "; { semi-colon suppresses the newline char }
end if
print book;
after group of id_number
skip 1 line
print column 1, "Phone: ", phone
skip 2 lines
end
Hope this helps!
Regards,
- Cathy
--------------------------------------------------------------------------------
Cathy Kipp e-mail: ckipp@vth1.vth.colostate.edu Phone: (303) 491-1294
Colorado State University Veterinary Teaching Hospital Fax: (303) 491-1205
Coming in late summer, from Prentice Hall, my new book:
Programming INFORMIX SQL/4GL: A Step-by-Step Approach (ISBN: 0-13-149394-9)