Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Insert Statement - Reports 2.5

Re: Insert Statement - Reports 2.5

From: <parkstate_at_my-deja.com>
Date: Wed, 25 Aug 1999 17:43:43 GMT
Message-ID: <7q1a0b$4mn$1@nnrp1.deja.com>


Probably it is difficult to implement that sum function in a single select statement. That is a common error which implies you are selecting a group value function, but not telling it on which column to group by(implicitly by selecting that column(s) in the select statement or explicitly by specifying group by). In any case, all the selected columns must be those on which the grouping funtions holds good.

An alternative.

In your report, create hidden field that calcluates Qty*Price(applying your null value rules) for every
record retreived and add a summary field to this(that holds the sum of (Qty*Price)). You can then use this summary field in your select statement. So it will look like this:

Item Qty Price (Qty*Price) -----> you can show/hide in the report

1        12      10      120
2        6       15       90

                         210  ------>  sum of Qty*Price(summary field)

Hope that helps!

PS

In article <7prp2k$6ad$1_at_nnrp1.deja.com>,   KRM <malleykr_at_my-deja.com> wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Aug 25 1999 - 12:43:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US