CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Sushmita shukla
Sushmita shukla

Reputation: 7

'ORA-00934: group function is not allowed here' while creating a Procedure

I have created a stored procedure, I was expecting to add the total column which is also a trigger, into FinalTotal. Code:

Procedure FINAL_TOTAL:

create procedure FINAL_TOTAL(C IN NUMBER,T OUT NUMBER)
        IS
        BEGIN
        UPDATE Products
        SET FinalTotal = SUM(Total),
        HAVING ProdCustId = C;
        Commit;
        SELECT SUM(FinalTotal) into T FROM Products WHERE ProdCustId = C;
        END;
        /

Here, if I write WHERE instead of HAVING then, still it's giving me the same error.

Trigger Total:

CREATE TRIGGER PROD_TOTAL
    AFTER INSERT ON Products
    BEGIN
    UPDATE Products
    SET Total = ProdPrice * ProdQuantity;
    END;
    /

Table Products;

create table Products
    ( ProdId number primary key,
      ProdNum number not null unique,
      ProdName varchar2(15),
      ProdPrice int,
      ProdQuantity int,
      Total int,
      FinalTotal int,
      ProdCustId int references Customers,
      ProdOrdId int references Orders,
      ProdStoreId int references Stores
    );

Error: 5/18 PL/SQL: ORA-00934: group function is not allowed here.

Since I am a beginner, I don't have that much idea, So If anyone knows why this is happening, then please let me know!!

Upvotes: 0

Views: 248

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21075

Please note first that you example should be questioned. You typically do not wan't to store a customer Id in a Product table as you often sell one product Id to lot of customers.

I'm therefore illustrating the concepts on a ProductSales table, while leaving only the most relevant attributes.

My main point is do not use triggers and procedures if you may use only plain SQL.

The first concept is the calculation of the Total Price - you may use the virtual column to calculate it

create table ProductSales
    ( ProdId number primary key,
      ProdPrice int,
      ProdQuantity int,
      Total int generated always as (ProdPrice * ProdQuantity) virtual,
      ProdCustId int  
    );
    
insert into  ProductSales (ProdId, ProdPrice, ProdQuantity,ProdCustId) values (1, 100, 5, 1001);   
insert into  ProductSales (ProdId, ProdPrice, ProdQuantity,ProdCustId) values (2, 50,  1, 1001);
insert into  ProductSales (ProdId, ProdPrice, ProdQuantity,ProdCustId) values (3, 100, 10,1002);

After adding some sample data you see, the Totalcolumn is calculated as expected and defined with the formula in the CREATE TABLE.

select * from ProductSales;

    PRODID  PRODPRICE PRODQUANTITY      TOTAL PRODCUSTID
---------- ---------- ------------ ---------- ----------
         1        100            5        500       1001
         2         50            1         50       1001
         3        100           10       1000       1002

The next feature ist the calculation of the total price per customer. Here I'd recommend to use an analytic function in a view as follows

create view V_ProductSales as         
select a.*,
sum(TOTAL) over (partition by ProdCustId) as CustTotal
from  ProductSales a;

select * from V_ProductSales;

   PRODID  PRODPRICE PRODQUANTITY      TOTAL PRODCUSTID  CUSTTOTAL
---------- ---------- ------------ ---------- ---------- ----------
         1        100            5        500       1001        550
         2         50            1         50       1001        550
         3        100           10       1000       1002       1000

So you get the customer total value from the view wihtout storing it in the table (which would violate the normal forms)

This is my final recommendation - get an introductory SQL book learn basic concepts (such as normalization, ER models, CamelCase Identifiers in Oracle, that price is not always Int datatype etc.) and than start your profesional career.

Upvotes: 1

Related Questions