create fact table in sql using materialized view

Question

I have 4 dimensions ('T_DATE', 'CUSTOMERS_COPY', and 'ORDERS_COPY') and I'm trying to create a fact table so far this is what I got but I got an error ( ORA-00904: "ID_C": invalid identifier ) any one have any Idea ??

CREATE MATERIALIZED VIEW F_ORDERS
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS SELECT ID_o,
          ID_s,
          ID_T,
          ID_C,
          SUM(Qty) AS Qty,
          SUM(Amount-Sum) AS Amount
   FROM T_DATE TD, CUSTOMERS_COPY CC, ORDERS_COPY OC, T_SALESMAN TS

   GROUP BY ID_O, ID_S, ID_T, ID_C;
   
ALTER MATERIALIZED VIEW F_ORDERS ADD CONSTRAINT PK_F_ORDERS 
PRIMARY KEY(ID_O, ID_S, ID_T, ID_C);

it's a stare schema/,,,,,,,,,,,,,,

Answer 1

With a star schema I would expect your query to look like this: (This assumes that the center of the star is an order)

   SELECT ID_o,
          ID_s,
          ID_T,
          ID_C,
          SUM(Qty) AS Qty,
          SUM(Amount-Sum) AS Amount
   FROM ordertable O
   LEFT JOIN T_DATE TD ON O.orderid  = TD.orderid 
   LEFT JOIN CUSTOMERS_COPY CC ON O.orderid = CC.orderid 
   LEFT JOIN ORDERS_COPY OC ON O.orderid = OC.orderid
   LEFT JOIN T_SALESMAN TS ON O.orderid = TS.orderid
   GROUP BY ID_O, ID_S, ID_T, ID_C;