How to calculate the sum of an item from a result set of a query.

Hi everyone,

I am using DEV2K v4.5 to develop a screen which perform a query data from
a table A to display a record at a time. One table A's field is a foreign key to a table B (one to many relationship). Here are example of this relationship:

Table A              Table B
rec_no               Order#
Name                  Item_no
Address              Quantity
Order#                 Amount

On the screen after query, it required the sum of Quantity, and Sum of the Amount of the query's result set are calculated and displayed. Is there any way to construct a dynamic SQL in Post-query (or any other way)
to perform this task.
Here is what I tried to do:

After the query is perform, I can extract the where clause from the SYSTEM.LAST_QUERY and store it in a varchar last_whereclause
(the content of this whereclause can be anything) . For example the
query was to select all orders >= 1000.
(SYSTEM.LAST_QUERY = SELECT REC_NO,NAME,ADDRESS,ORDER# FROM A WHERE
ORDER# >= 1000). What I want to do in the post-query is somehow to execute the select statement:
 SELECT SUM(QUANTITY) FROM B WHERE ORDER# IN (SELECT ORDER# FROM A WHERE ORDER# >= 1000).
The WHERE clause in the inner SELECT statement can be anything. (that's why I extracted the
WHERE clause in SYSTEM.LAST_QUERY).

My problem is how to combine the var last_whereclause into that statement when building
a cursor (cursor declaration does not accept the sign ||).

Can anyone help me or direct me to some other way to solve this problem.

Thanks in advance.

Quang Vu Received on Wed May 06 1998 - 23:48:46 CDT