Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql with amount due
pl/sql with amount due [message #570522] Sun, 11 November 2012 17:38 Go to next message
seenpeepo
Messages: 1
Registered: November 2012
Junior Member
just begin learning pl/sql. I have question I work but it is not correct.
The question is I have 4 tables one for customer, one for order line, one for inventory on one for customer order.

I need to print the name of each customer and also how much money they owed because of balance they have because of their purchase. At the end I need to get total amount due for all customers

This is what I did so far
SET SERVEROUTPUT ON;
DECLARE
CURSOR CURSco IS
SELECT first, last,OL.order_ID,sum(ol.order_quanitity*price)
FROM
            order_line OL
            JOIN cust_order CO ON OL.order_id = CO.order_id
            JOIN customer CS ON CO.cust_id = CS.cust_id
            JOIN inventory I ON OL.inv_id = I.inv_id;
            cus_row customer%ROWTYPE;
            Totalo Number(9,2) :=sum( ol.order_quanitity*price);
            FOR cus_row IN CURSco LOOP
dbms_output.put_line(cus_row.first||' ' ||cus_row.last||''||cus_row.Totalo);
 
end loop;
end;
/



Re: pl/sql with amount due [message #570523 is a reply to message #570522] Sun, 11 November 2012 18:02 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
Since we don't have your tables or data, we can not compile, run or test posted code.
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: pl/sql with amount due [message #570536 is a reply to message #570522] Sun, 11 November 2012 23:46 Go to previous message
Michel Cadot
Messages: 58856
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you etoilethay?

Regards
Michel
Previous Topic: determine number of times a value appears - sql
Next Topic: please help with my code PL/SQL Block
Goto Forum:
  


Current Time: Thu Aug 21 18:59:28 CDT 2014

Total time taken to generate the page: 0.05200 seconds