Calculate a total when creating a table using the attributes from another one

Question

I did this and it works:

CREATE TABLE OrderLine 
(
    Invoice_ID varchar2(10) PRIMARY KEY NOT NULL,
    Item_ID varchar2(10) NOT NULL,
    Unit_Price number(6,2) NOT NULL,
    Quantity number(6) NOT NULL,
    Total_Price number(38,2) AS (CASE WHEN Quantity >= 50 THEN 
    ((Unit_Price*Quantity*0.80) ELSE (Unit_Price*Quantity) END),
    CONSTRAINT fk_item_invoice FOREIGN KEY(Invoice_ID) REFERENCES Invoice (Invoice_ID),
    CONSTRAINT fk_item_item FOREIGN KEY(Item_ID) REFERENCES Item (Item_ID),
    CONSTRAINT Unit_Price_CK CHECK (Unit_Price > 0),
    CONSTRAINT Quantity_CK CHECK (Quantity > 0),
);

CREATE TABLE Invoice 
(
     Invoice_ID varchar2(10) PRIMARY KEY NOT NULL,
     Office_ID varchar2(7) NOT NULL,
     Invoice_Date Date NOT NULL,
     Total_Cost number (9) NOT NULL,
     CONSTRAINT fk_order_Office FOREIGN KEY(Office_ID) REFERENCES Office (Office_ID),
     CONSTRAINT Total_Cost_CK CHECK (Total_Cost > 0)
);

I want to calculate the TotalCost like the sum of TotalPrice(s) when I create the table, not via a query. Is that possible ?

PS: I'm using SQL Developer.

Your question makes no sense. Queries are how you extract information from a database. And the sums are `NULL` when you create the table, because there are no rows. — Gordon Linoff, Feb 04 '18 at 15:40
This is a bad design. You have hardcoded a business rule (discount for bulk purchases) into the structure of the table. This is a very poor use of virtual columns. That aside, a virtual column is defined against the columns of its table: we can't reference columns on other tables, which is what you need to sum `TotalPrice`. — APC, Feb 04 '18 at 18:08
Possible duplicate of https://stackoverflow.com/questions/13488822/create-computed-column-using-data-from-another-table — Vidmantas Blazevicius, Feb 04 '18 at 20:03

giorgiga · Answer 1 · 2018-02-05T11:35:26.787

I guess what you want is to define a view on your existing tables:

create or replace view v_invoice as
select i.invoice_id,
       i.office_id,
       i.invoice_date,
       i.total_cost,
       sum(l.total_price) as total_price,
       count(l.id) as lines
  from invoice i
       left join orderline l on l.invoice_id = i.invoice_id -- maybe "inner" join?
 group by i.invoice_id, i.office_id, i.invoice_date, i.total_cost
;

disclaimer: I think the above works in Oracle, but I have no instance at hand to check

You can think of a view either as a saved query or as an "automatic" table and use it as such:

select * from v_invoice;

You are missing (at least) an identifier for order lines (which I guess should be called "invoice lines"). :-)

This won't work for Oracle because Oracle requires all non-aggregated columns to be included in the GROUP BY clause. — APC, Feb 04 '18 at 18:03

Calculate a total when creating a table using the attributes from another one

1 Answers1