CODEWITHSUNDEEP
sqloracle-database
user5041486
user5041486

Reputation:

Returns More Than Requested Rows

I have this PL/SQL Function:

Create Or Replace Function mostMealOrders
Return varchar
Is

name_phone varchar(200) := ' ';

Cursor c1 Is
        Select acctid  
        From MealOrder Natural Join Customer
        Group By acctid
        Having Count(*) >= All (Select Count(*)
                                From MealOrder
                                Group By acctid);

Begin
        For acctNum in c1
        Loop
                Select (name || ' ' || phone || ', ')
                Into name_phone
                From Customer
                Where acctid = acctNum.acctid;
        End Loop
        Return name_phone;
End;
/
Show Errors;

When I run the above code I receive this error:

exact fetch returns more than requested number of rows

The subquery tries to find the acctId with the most number of orders from the OrderTable.

The subquery is returning more than one acctid and therefore I believe that is why I am getting the error. Does anyone know how I can grab the name and phone of all the acctid's returned from the subquery as I am trying to do in the outer query?

Upvotes: 0

Views: 56

Answers (1)

trincot
trincot

Reputation: 350270

You could use a cursor, like this:

FOR rec in (
    Select (name || ' ' || phone || ',') AS name_phone
    From Customer
    Where acctid in (Select acctid    
                     From OrderTable Natural Join Customer
                     Group By acctid
                     Having Count(*) >= All (
                                Select Count(*)
                                From OrderTable
                                Group By acctid))
) LOOP
     -- do something with rec.name_phone
     dbms_output.put_line(rec.name_phone);
END LOOP;

If however, you are looking for a loop that concatenates all retrieved strings to one long string, then use the LISTAGG function:

Select LISTAGG (name || ' ' || phone, ',') 
       WITHIN GROUP (ORDER BY name, phone)
Into name_phone
From Customer
Where acctid in (Select acctid    
                 From OrderTable Natural Join Customer
                 Group By acctid
                 Having Count(*) >= All (
                            Select Count(*)
                            From OrderTable
                            Group By acctid))

Upvotes: 1

Related Questions