Insert Statement - Reports 2.5

Hello.

I am having a problem with one of my reports. After the report runs, it is supposed to take information that appeared in each report, and insert it into another table. There should be one insertion for each report that prints (the report generates invoices, so if 3 invoices are created, 3 records should be inserted into the other table.)

I think I have figured out most of what I want to do. I am planning to put the insert into the AfterReport trigger. I am using the form of the Insert statement that will allow me to use a Select statement to get the values. This is where I am running into problems.

I have 95% of the select statement working. However, there is one value that I am not able to get. This value is the total amount of the invoice. It is calculated in the report by a summation of multiplications.

For instance, if there are three items on the invoice:

Item Qty Price

1    1   10.00
2    3    5.00
3    1    2.00

This field will equal (1*10) + (3*5) + (1*2). Also, there are occurances where the Qty may be null, in which case, it should be replaced by 1. The following is the statement that I am trying to use:

(SUM(NVL(QTY, 1) * PRICE)) I have not yet tried the select statement from within Oracle. Right now, I'm using QVTNet to connect to the database, and run PL/SQL statements.

Whenever I put in the 'Sum' into the select statement, I get errors telling me:

ERROR at line 1:
ORA-00937: not a single-group group function

Is what I am trying to do possible? Am I making any errors with the other things I am doing? Any suggestions are greatly appreciated.

Thanks!
Kristen

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 23 1999 - 10:24:21 CDT