CODEWITHSUNDEEP
oracle-databaseplsql
Cc Max
Cc Max

Reputation: 1

Getting the number of rows with same date in a procedure (PL/SQL)

I have been working this for a while now, and I am confused as to why I am getting "exact fetch returns more than requested number of rows" error. I even followed the answer for this Udemy course but that was giving the same error as well.

This is the code:

CREATE OR REPLACE PROCEDURE GET_SALE_COUNT
(
    c_order_id  IN NUMBER,
    c_count_sales OUT NUMBER
)
AS
    c_sales_date sales.sales_date%TYPE;
    c_product_id sales.product_id%TYPE;
    c_customer_id sales.customer_id%TYPE;
    c_salesperson_id sales.salesperson_id%TYPE;
    c_quantity sales.quantity%TYPE;
    c_unit_price sales.unit_price%TYPE;
    c_sales_amount sales.sales_amount%TYPE;
    c_tax_amount sales.tax_amount%TYPE;
    c_total_amount sales.total_amount%TYPE;
BEGIN

SELECT sales_date,product_id,customer_id,salesperson_id,quantity,unit_price,sales_amount,tax_amount,total_amount 
    INTO c_sales_date,c_product_id,c_customer_id,c_salesperson_id,c_quantity,c_unit_price,c_sales_amount,c_tax_amount,c_total_amount 
    FROM sales WHERE c_order_id = sales.order_id;
    
DBMS_OUTPUT.put_line('Sales Date: ' || c_sales_date);
DBMS_OUTPUT.put_line('Product ID: ' || c_product_id);
DBMS_OUTPUT.put_line('Customer ID: ' || c_customer_id);
DBMS_OUTPUT.put_line('Salesperson ID: ' || c_salesperson_id);
DBMS_OUTPUT.put_line('Quantity: ' || c_quantity);
DBMS_OUTPUT.put_line('Unit Price: ' || c_unit_price);
DBMS_OUTPUT.put_line('Sales Amount: ' || c_sales_amount);
DBMS_OUTPUT.put_line('Tax Amount: ' || c_tax_amount);
DBMS_OUTPUT.put_line('Total Amount: ' || c_total_amount);


SELECT COUNT(1) INTO c_count_sales FROM sales WHERE sales_date = c_sales_date;
END GET_SALE_COUNT;

DECLARE
count_sales NUMBER;
BEGIN
    GET_SALE_COUNT(1267, count_sales);
    DBMS_OUTPUT.put_line('Sale Count: ' || count_sales);
END

;

Upvotes: 0

Views: 57

Answers (1)

Littlefoot
Littlefoot

Reputation: 142883

If your task is to count number of rows, why are you doing other unnecessary stuff? Because, it is the first select you wrote that returns more than exactly one row (and returns TOO_MANY_ROWS error). Why did that happen? Because not only one row exists for c_order_id parameter you pass. Run that statement out of the procedure and you'll see.

Therefore, as your task isn't to do what you did, skip it and use something like this:

create or replace procedure get_sale_count
  (c_order_id    in  number,
   c_count_sales out number
  )
is
begin
  select count(*)
    into c_count_sales
    from sales a
    where a.sales_date in (select b.sales_date
                           from sales b
                           where b.order_id = c_order_id);
end;
/

If you do need to display various data, then do it in a loop.

Upvotes: 1

Related Questions