LP496 LP496 - 7 months ago 8
SQL Question

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?

Answer

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))