Re: SQL query - help please
In article <35AD8DA9.C9E9D285@pop.phnx.uswest.net>,
Ken Vaughn <kvaughn@uswest.net> wrote:
>A little known feature in Informix 4GL is that you can open, output to and
>finish a second, third, etc. report from within another report. You could,
>if you desired, do a start report in a before group of sales_rep clause,
Yes, I have done this and it works well for me. For instance, I generate
a report that looks like this:
Territory: 658
--------------
Transact Product Customer $ Amt
num ID num ID num
-------- ------- -------- --------
3985870 388966 1142522 83,588.00
3985871 388966 1142447 102,467.00
3985872 388966 3498 86,625.00
3985873 388966 10599 5,500.00
3985874 388966 6336 166,650.00
3985875 388966 1142551 296,970.00
3985869 388966 10309 155,150.00
3985876 388966 10972 69,086.00
3985885 388966 3498 86,625.00
3985877 388966 10620 450,000.00
3985878 388966 1142525 1,010,825.00
3985884 388966 3498 86,625.00
3985883 388966 3498 86,625.00
3985882 491436 1127066 -750.00
____________
Total ....................... 2,685,986.00
Totals by Product
Product $ total
------- -------
388966 2,686,736.00
491436 -750.00
______________
Total ..... 2,685,986.00
Totals by Customer
Customer $ total
-------- -------
3498 346,500.00
6336 166,650.00
10309 155,150.00
10599 5,500.00
10620 450,000.00
10972 69,086.00
1127066 -750.00
1142447 102,467.00
1142522 83,588.00
1142525 1,010,825.00
1142551 296,970.00
______________
Total ..... 2,685,986.00
Territory: 553
--------------
Transact Product Customer $ Amt
num ID num ID num
-------- ------- -------- --------
3985880 388966 1142549 5,840.00
3985879 388966 1142549 11,100.00
____________
Total ....................... 16,940.00
Totals by Product
Product $ total
------- -------
388966 16,940.00
______________
Total ..... 16,940.00
Totals by Customer
Customer $ total
-------- -------
1142549 16,940.00
______________
Total ..... 16,940.00
with the following 4GL (which is quick-and-dirty and for demo purposes
only):
database <DBname>
main
define p_product_id like TABLE_A.product_id
define p_territory_id like TABLE_A.territory_id
define p_customer_id like TABLE_A.customer_id
define p_ship_dollars like TABLE_A.ship_dollars
define p_ext_id integer
declare c1 cursor for
select ext_id, product_id, territory_id, customer_id, ship_dollars
from TABLE_A
order by territory_id desc
start report r1 to "r1.out"
foreach c1 into p_ext_id,
p_product_id, p_territory_id, p_customer_id, p_ship_dollars
output to report r1
( p_ext_id, p_product_id, p_territory_id, p_customer_id, p_ship_dollars )
end foreach
finish report r1
end main
report r1
( rpt_ext_id, rpt_product_id, rpt_territory_id, rpt_customer_id,
rpt_ship_dollars )
define rpt_ext_id integer
define rpt_product_id like TABLE_A.product_id
define rpt_territory_id like TABLE_A.territory_id
define rpt_customer_id like TABLE_A.customer_id
define rpt_ship_dollars like TABLE_A.ship_dollars
order external by rpt_territory_id
format
before group of rpt_territory_id
print "Territory: ", rpt_territory_id using "<<<<<"
print "--------------"
skip 1 line
print column 14, "Transact Product Customer $ Amt"
print column 14, "num ID num ID num "
print column 14, "-------- ------- -------- --------"
skip 1 line
start report by_product to "by_product.out"
start report by_customer to "by_customer.out"
on every row
print column 10, rpt_ext_id,
rpt_product_id,
rpt_customer_id,
rpt_ship_dollars using "---,---,--&.&&"
output to report by_product ( rpt_product_id, rpt_ship_dollars )
output to report by_customer ( rpt_customer_id, rpt_ship_dollars )
after group of rpt_territory_id
print column 45, "____________"
print column 14, "Total .......................",
group sum(rpt_ship_dollars) using "---,---,--&.&&"
finish report by_product
skip 2 line
print file "by_product.out"
skip 2 lines
finish report by_customer
skip 2 line
print file "by_customer.out"
skip 2 lines
end report { r1 }
report by_product ( rpt2_product_id, rpt2_ship_dollars )
define rpt2_product_id integer
define rpt2_ship_dollars like TABLE_A.ship_dollars
output
page length 7
top margin 0
bottom margin 0
order by rpt2_product_id
format
first page header
print column 15, "Totals by Product"
skip 1 line
print column 15, "Product $ total"
print column 15, "------- -------"
skip 1 line
after group of rpt2_product_id
print column 11, rpt2_product_id, 4 spaces,
group sum (rpt2_ship_dollars) using "---,---,--&.&&"
on last row
print