Customer's Credit Limit

Hi there,

I am getting confused here with my sql code.

I am using Oracle PL/SQL Gen8i & have two tables. (See atttachment, SQL create tables plus data) One customer table and one orders table

Where i am getting confused is with the query: "The PL/SQL block to raise each customers' credit limit by the equal value to the average ammount of his or her orders." My issue is particularly with the SQL (select) in the declaration statement. (You don't need to know PL/SQL to answer this)
.
As I understand it, this means finding the total nos of orders per customer and the total ammount (from balance) of the orders, finding the average and adding this average to the credit limit.

The problem is that I am not sure how to count the nos of customer orders in SQL across the two tables in order to calculate the average ammount per order. Not every customer has made an order. (this exception will be handled by PLSQL later). I think i should use joins but they don't seem to work.

I only need to display the follow in PLSQL via DBMS_OUTPUT_LINE is the following
----------------------------------------------------------------------
custnumb custname oldcredlim newcredlim

This translates in SQL to from a SQL select query, in incrememtal steps

1) SELECT current creditlimit and orders for all customers from TABLES customer & orders.
2) COUNT the nos of orders per customer
3) SUM the total balances of orders
4) Calculate new credit limit = Total Balance / Nos of Orders

If you know how a cursor for the PLSQL block would work for this that would be a bonus, if not just stick to the SQL query part of the problem.

I know how it works at the conceptual level but get bogged down when i try the SQL script ... am working alone so in need of some support.

yours fustrated,
Charlie

charlie@rahinston.com

charlie, the sql is straightforward if you take it in stages

select custno, sum(balance)/count(custno) as avgorder
from orders
group by custno

this query gives you the average order balance per customer number

a left join from customer table to order table in the above query could also be used, but it would need some adjustment to handle situations where a customer had no orders, but i'm guessing you don't actually need to know, because if you're really adding the average to the existing credit limit, you'd be adding zero anyway, so you don't actually need to update those customers

your requirement to print out all customers with old and new credit limits sounds suspiciously like homework, so i'll skip over that part, and move on to the update

update customers
set creditlimit = creditlimit +
(select select sum(balance)/count(custno)
from orders where custno = customers.custno)
where custno in (select distinct custno from orders)

here the grouping is performed by correlation

the correlated subquery matches the custno of the orders table to the custno of the customers table in the outer update query, in effect forming a group for each customer.custno

the subquery in the WHERE clause ensures that the UPDATE occurs only for customers who had orders, because there's no point in updating those who didn't

you do not need a cursor, you do not need to loop, you do not need to count the orders or sum their balances, or try to match the averages to the customer table yourself

helps?

rudy
http://r937.com/

Hi Rudy,

It was late and i was fustrated with the code ... many thanks for the assist ... The problem is homework of a kind, own self taught stuff. I am using PL/SQL to use procedures and the likes to automate a test database with the use of cursors. The only way i thought it could be done was via PL/SQL .... interesting you went as far as writing the same code in SQL.

Funny thing is that the more complex opperations in PLSQL I have a good understanding off, but the simple SQL stuff i get tripped up over because their are several workarounds for then same result.

Many thanks and hope that you can further assit if need be.

Charlie