Splunk Splunk - 2 months ago 24
SQL Question

PL/SQL Group By - ORA-01422: exact fetch returns more than requested number of rows

I am writing the following query that I want to display car registration, car group name, model name, cost and the number of bookings for each car. I have to use an explicit cursor and I have to use an implicit cursor to calculate the number of bookings that belong to each car.

My query is as follows:

SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000
Declare
v_count number;
cursor carcur IS
SELECT * FROM i_car;
v_car carcur%ROWTYPE;
Begin
Select COUNT (registration)
INTO v_count
from i_booking
group by registration;
FOR v_car IN carcur LOOP
DBMS_OUTPUT.PUT_LINE('Registration:'|| ' '|| v_car.registration);
DBMS_OUTPUT.PUT_LINE('Car Group:'|| ' ' ||v_car.car_group_name);
DBMS_OUTPUT.PUT_LINE('Model Name:'|| ' '||v_car.model_name);
DBMS_OUTPUT.PUT_LINE('Cost:'|| ' '||v_car.cost);
DBMS_OUTPUT.PUT_LINE('Total Bookings:'|| ' '||v_count);
DBMS_OUTPUT.NEW_LINE;

END LOOP;
End;


The output I am getting is as follows:
Declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 7

I am sure it has something to do with the return values being put into the variable, but I have no idea how to rectify this.

Any advice would be greatly appreciated.

Many thanks.

Answer

This did the trick for me. I moved my implicit SELECT statement into the cursor for loop and added a WHERE clause saying WHERE registration = v_car.registration;

SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000
    Declare
    v_count number;
    cursor carcur IS
    SELECT * FROM i_car;
    v_car carcur%ROWTYPE;
    Begin
    FOR v_car IN carcur LOOP
    Select COUNT (registration)
    INTO v_count
    from i_booking
    WHERE registration = v_car.registration;
    DBMS_OUTPUT.PUT_LINE('Registration:'|| '  '|| v_car.registration);
    DBMS_OUTPUT.PUT_LINE('Car Group:'|| ' ' ||v_car.car_group_name);
    DBMS_OUTPUT.PUT_LINE('Model Name:'|| ' '||v_car.model_name);
    DBMS_OUTPUT.PUT_LINE('Cost:'|| ' '||v_car.cost);
    DBMS_OUTPUT.PUT_LINE('Total Bookings:'|| ' '||v_count);
    DBMS_OUTPUT.NEW_LINE;
    END LOOP;
    End;

Many thanks to everyone for you assistance.

Comments