Create a table "ca" as a statement using values of another tables but while inserting new-CodePudding

I want to calculate turnover like in new table called ca '

create table ca   as (select sum(a.prixvente * c.qte)as ca , c.datec as datee  from article a , command c where a.ida = c.ida and c.datec
= c.datec group by c.datec  ) ;

I have tables article (idarticle, priceBuying, priceSale, quantity), and table command have (idarticle, quantity, dateSale), I want to calculate turnover automatically everyday and insert into ca , but the problem is when I insert new values into command the values of ca not updating using oracle db.

CodePudding user response：

create table as select creates a table and populates it with the data from the select at the time it was run. There is nothing that would automatically update the table going forward.

You could certainly write code that would insert new data for subsequent days (and/or update older rows) and then schedule that code to run at a reasonable point in time (i.e. midnight or 2am or something).

My guess, however, is that you really want to create a view rather than a table. A view is just a stored query so it would be re-run every time you queried the view.

create or replace view ca   
as 
select sum(a.prixvente * c.qte)as ca , 
       c.datec as datee  
  from article a , 
       commande c 
 where a.ida = c.ida 
   and c.datec = c.datec 
 group by c.datec

You could also potentially want to create a materialized view that was scheduled to automatically refresh on a particular schedule.