nvidia
01-16-2007, 04:18 PM
Hi i am trying to calculate the total order (total price of orders) in my database. To do this the user enters an order id
I need to use 3 tables for this, customer, orders and orderline table:
CREATE TABLE customer
(custid NUMBER(5),
last VARCHAR2(30) CONSTRAINT customer_last_nn NOT NULL,
first VARCHAR2(30),
mid_initial CHAR(1),
cadd VARCHAR2(30),
town VARCHAR2(30),
city VARCHAR2(30),
postcode VARCHAR2(10),
dphone VARCHAR2(10),
ephone VARCHAR2(10),
CONSTRAINT customer_custid_pk PRIMARY KEY (custid));
CREATE TABLE orders
(orderid NUMBER(8),
orderdate DATE CONSTRAINT orders_orderdate_nn NOT NULL,
methpmt VARCHAR2(10) CONSTRAINT orders_methpmt_nn NOT NULL,
custid NUMBER(5),
ordersource VARCHAR2(20),
CONSTRAINT orders_orderid_pk PRIMARY KEY (orderid),
CONSTRAINT orders_custid_fk FOREIGN KEY (custid) REFERENCES customer(custid),
CONSTRAINT orders_ordersource_fk FOREIGN KEY (ordersource) REFERENCES ordersource(ordersource));
CREATE TABLE orderline
(orderid NUMBER(8) CONSTRAINT orderline_orderid_nn NOT NULL,
invid NUMBER(10) CONSTRAINT orderline_invid_nn NOT NULL,
order_price NUMBER(6,2) CONSTRAINT orderline_order_price_nn NOT NULL,
quantity NUMBER(4) CONSTRAINT orderline_quantity_nn NOT NULL,
CONSTRAINT orderline_pk PRIMARY KEY (orderid, invid),
CONSTRAINT orderline_orderid_fk FOREIGN KEY (orderid) REFERENCES orders(orderid),
CONSTRAINT orderline_invid_fk FOREIGN KEY (invid) REFERENCES inventory(invid));
Insert statements:
INSERT INTO CUSTOMER VALUES (107, 'HARRIS', 'PAULA', 'E', '1156 WATER STREET', 'KENSINGTON','LONDON', 'SW1 6DS', 02071555589, 02071555590);
INSERT INTO ORDERS VALUES (1057, TO_Date('05/29/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'CC', 107, '152');
INSERT INTO ORDERLINE VALUES (1058, 11824, 15.99, 1);
My code:
SET SERVEROUTPUT ON;
DECLARE
v_odNum NUMBER(8):= &sv_orderNum;
v_price NUMBER(7,2);
v_quantity NUMBER(5);
v_fname VARCHAR2(30);
v_lname VARCHAR2(30);
v_add VARCHAR2(30);
BEGIN
SELECT first, last, cadd, order_price, quantity, SUM(order_price * quantity) AS TOTAL
INTO v_fname, v_lname, v_add, v_price, v_quantity
FROM customer c, orders o, orderline ordl
WHERE c.custid = o.custid AND o.orderid = ordl.orderid
AND v_odNum = o.orderid
DBMS_OUTPUT.PUT_LINE('The total order for' ||v_odNum|| ' '|| 'is' || TOTAL);
END;
/
Is there anyway of using the SUM function so that i can store the result in a variable and display it similar to the ouput code??
I need to use 3 tables for this, customer, orders and orderline table:
CREATE TABLE customer
(custid NUMBER(5),
last VARCHAR2(30) CONSTRAINT customer_last_nn NOT NULL,
first VARCHAR2(30),
mid_initial CHAR(1),
cadd VARCHAR2(30),
town VARCHAR2(30),
city VARCHAR2(30),
postcode VARCHAR2(10),
dphone VARCHAR2(10),
ephone VARCHAR2(10),
CONSTRAINT customer_custid_pk PRIMARY KEY (custid));
CREATE TABLE orders
(orderid NUMBER(8),
orderdate DATE CONSTRAINT orders_orderdate_nn NOT NULL,
methpmt VARCHAR2(10) CONSTRAINT orders_methpmt_nn NOT NULL,
custid NUMBER(5),
ordersource VARCHAR2(20),
CONSTRAINT orders_orderid_pk PRIMARY KEY (orderid),
CONSTRAINT orders_custid_fk FOREIGN KEY (custid) REFERENCES customer(custid),
CONSTRAINT orders_ordersource_fk FOREIGN KEY (ordersource) REFERENCES ordersource(ordersource));
CREATE TABLE orderline
(orderid NUMBER(8) CONSTRAINT orderline_orderid_nn NOT NULL,
invid NUMBER(10) CONSTRAINT orderline_invid_nn NOT NULL,
order_price NUMBER(6,2) CONSTRAINT orderline_order_price_nn NOT NULL,
quantity NUMBER(4) CONSTRAINT orderline_quantity_nn NOT NULL,
CONSTRAINT orderline_pk PRIMARY KEY (orderid, invid),
CONSTRAINT orderline_orderid_fk FOREIGN KEY (orderid) REFERENCES orders(orderid),
CONSTRAINT orderline_invid_fk FOREIGN KEY (invid) REFERENCES inventory(invid));
Insert statements:
INSERT INTO CUSTOMER VALUES (107, 'HARRIS', 'PAULA', 'E', '1156 WATER STREET', 'KENSINGTON','LONDON', 'SW1 6DS', 02071555589, 02071555590);
INSERT INTO ORDERS VALUES (1057, TO_Date('05/29/2001 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'CC', 107, '152');
INSERT INTO ORDERLINE VALUES (1058, 11824, 15.99, 1);
My code:
SET SERVEROUTPUT ON;
DECLARE
v_odNum NUMBER(8):= &sv_orderNum;
v_price NUMBER(7,2);
v_quantity NUMBER(5);
v_fname VARCHAR2(30);
v_lname VARCHAR2(30);
v_add VARCHAR2(30);
BEGIN
SELECT first, last, cadd, order_price, quantity, SUM(order_price * quantity) AS TOTAL
INTO v_fname, v_lname, v_add, v_price, v_quantity
FROM customer c, orders o, orderline ordl
WHERE c.custid = o.custid AND o.orderid = ordl.orderid
AND v_odNum = o.orderid
DBMS_OUTPUT.PUT_LINE('The total order for' ||v_odNum|| ' '|| 'is' || TOTAL);
END;
/
Is there anyway of using the SUM function so that i can store the result in a variable and display it similar to the ouput code??