Re: ACE Reports
In article <42q4mj$rul@murphy.servtech.com>,
bob dreste <radreste@cyber1.servtech.com> wrote:
>I am writing ACE reports in informix. My questions is (and maybe I'm
>missing something here) I there any way to write multiple select statements
>in the same ACE report. From what I've seen you can only have one select
>statement and one print section. For instance, lets say I wanted to dump
>all my customers and all my inventory items using 1 ACE report. How would
>this be accomplished (or would I have to write 2 seperate reports) ? ThANKs
>FOR ANY HELP !!!
I would tend to write two different reports. However, if you wanted to do
both lists in one report then you could, perhaps like this:
database nsoe
end
output
left margin 0
right margin 0
report to "t.out"
end
select cust_id, company
from cust_info
into temp customers with no log ;
select prod_12, shortname, model
from prod_mast
into temp inventory with no log ;
select "part 1" section, cust_id, company, '' prod_12, '' shortname, '' model
from customers
union
select "part 2", -1, '', prod_12, shortname, model
from inventory
into temp both ;
select *
from both
order by section
end
format
before group of section
print ""
print "------------------------------------------------------------"
if ( section = 'part 1' ) then
print "all our lovely customers"
else
print "all our lovely products"
on every row
if ( section = 'part 1' ) then
print cust_id, 2 spaces, company
else
print prod_12, 2 spaces, shortname, 2 spaces, model
end
which outputs the following:
------------------------------------------------------------
all our lovely customers
2 PAUL'S MANAGEMENT ASSOCIATES INC
11 ROBERTS INSURANCE COMPANY
29 ACME WIDGET
------------------------------------------------------------
all our lovely products
012380131411 C-ISAM JAPANESE AVIION 100
012380131421 C-ISAM JAPANESE AVIION 200D
012380131401 C-ISAM JAPANESE AVIION 310
012380131431 C-ISAM JAPANESE AVIION 210
012380131491 C-ISAM JAPANESE AVIION 530
012380131441 C-ISAM JAPANESE AVIION 300
012380131451 C-ISAM JAPANESE AVIION 400
012380131461 C-ISAM JAPANESE AVIION 402
012380131471 C-ISAM JAPANESE AVIION 410
012380131481 C-ISAM JAPANESE AVIION 412
As you see, I kludge it a bit so that both sets of data end up being
selected with a single select. You have to 'pad' each one of the data
sets with dummy elements for the other data set, because the UNION
demands that the number and type of the columns be compatible.
You can certainly get away with fewer temp tables than I use above,
I use them because I think it makes it clearer what is going on.
- Paul