i am trying to display the number of orders for each customer as a dbms_output.put_line but the count is not correct...what am I doing wrong??
declare
t_order number;
cust_id customer.c_id%type;
orders_cid orders.c_id%type;
cust_first customer.c_first%type;
cust_last customer.c_last%type;
cursor c is
select distinct(customer.c_id),
customer.c_first,
customer.c_last,
orders.c_id
from customer
join orders
on customer.c_id = orders.c_id;
begin
dbms_output.put_line('AZ SPORTS OUTSTANDING ORDERS');
dbms_output.put_line('FIRST LAST TOTAL');
dbms_output.put_line('NAME NAME ORDERS');
OPEN C;
LOOP
FETCH C INTO CUST_ID, CUST_FIRST, CUST_LAST, ORDERS_CID;
EXIT WHEN C%NOTFOUND;
IF CUST_ID=ORDERS_CID THEN
select count(distinct(orders.c_id))
into t_order
from orders
join customer
on orders.c_id = customer.c_id
DBMS_OUTPUT.PUT_LINE(TO_CHAR(CUST_FIRST) || ' ' ||
TO_CHAR(CUST_LAST) || ' ' ||
TO_CHAR(T_ORDER));
END IF;
END LOOP;
CLOSE C;
END;
/
The number of orders per customer is displaying 5 all the way down for each customer which isnt the correct answer.
Looks to me like you need a WHERE clause on the SELECT COUNT...
statement - perhaps something like WHERE ORDERS.C_ID = ORDERS_CID AND CUSTOMER.C_ID = CUST_ID
. Try that and see how it works.
Share and enjoy.