CODEWITHSUNDEEP
sqloracle-databaseplsql
J.C
J.C

Reputation: 15

How to fix procedure PL SQL Errors: 'not a single-group group function' & 'invalid number'

Program must display the 10 dogs with the most Tricks learned in that m0nth: OUTPUT Dog Name, ID NO and NO of tricks learned in that particular month based on (P_month), when I comment out the ORDER BY clause it runs but then when I enter a paramater eg. 'april' it says: ORA -1722'invalid number'

create or replace 
PROCEDURE DOGS (P_MONTH IN VARCHAR2) IS

CURSOR CUR_DOGS (CP_MONTH VARCHAR2) IS
SELECT DR.DOG_NAME, TR.DOG_ID
FROM DOG DR, TRICK TR
WHERE TRIM(INITCAP(TO_CHAR(TR.DATE_OF_TRICK, 'month'))) = TRIM(INITCAP(TO_CHAR(CP_MONTH, 'month')))
AND DR.DOG_ID = TR.DOG_ID
ORDER BY COUNT (TR.DOG_ID) DESC;

REC_DOGS CUR_DOGS%ROWTYPE;
V_NO_TRICKS NUMBER;

BEGIN

DBMS_OUTPUT.PUT_LINE ('DOG NAME' || ' ' || 'ID NO' || ' ' || 'NO OF TRICKS');

OPEN CUR_DOGS (P_month);
LOOP
FETCH CUR_DOGS INTO REC_DOGS;
EXIT WHEN (CUR_DOGS%ROWCOUNT > 10) OR (CUR_DOGS%NOTFOUND);

SELECT COUNT(TRICK_NO)INTO V_NO_TRICKS FROM TRICK  WHERE DOG_ID = REC_DOGS.DOG_ID;

DBMS_OUTPUT.PUT_LINE (REC_DOGS.DOG_NAME ||' '||REC_DOGS.DOG_ID ||' '|| V_NO_TRICKS);

END LOOP;
CLOSE CUR_DOGS;

END;

Upvotes: 0

Views: 164

Answers (1)

Luke Woodward
Luke Woodward

Reputation: 64969

The reason you get an invalid-number error is in this part of your query:

 TRIM(INITCAP(TO_CHAR(CP_MONTH, 'month')))

Cursor parameter cp_month is already a VARCHAR2, so there's no need to convert it using TO_CHAR. Replace this with 

 TRIM(INITCAP(CP_MONTH))

The reason you get a not a single-group group function error is that you are using an aggregate function (COUNT) incorrectly.

You can use COUNT to count the number of rows in a table, e.g. SELECT COUNT(*) FROM some_table. This returns a single row with the number of rows in the table some_table. But what if you write something like SELECT some_column, COUNT(*) FROM some_table? Oracle still has to return a single row, but it doesn't know what value to return for some_column because there may be more than one row. Which row would be correct?

You have to tell Oracle to calculate one count per dog, rather than one count overall. You do this by specifying a GROUP BY clause. This lists all the columns to group by.

Going back to our previous example, we could write

SELECT some_column, COUNT(*) FROM some_table GROUP BY some_column

This returns one row for each distinct value of the some_column column, along with the number of times each value occurred.

When using GROUP BY, you need to group by all columns that you are selecting. In your case, this is DR.DOG_NAME and TR.DOG_ID. You may be tempted to just group by TR.DOG_ID since the name of the dog is determined by its ID (presumably a primary key). However, Oracle won't let you do this.

So, your cursor query should look like the following:

CURSOR CUR_DOGS (CP_MONTH VARCHAR2) IS
SELECT DR.DOG_NAME, TR.DOG_ID
FROM DOG DR, TRICK TR
WHERE TRIM(INITCAP(TO_CHAR(TR.DATE_OF_TRICK, 'month'))) = TRIM(INITCAP(CP_MONTH))
AND DR.DOG_ID = TR.DOG_ID
GROUP BY DR.DOG_NAME, TR.DOG_ID       -- Add this line
ORDER BY COUNT (TR.DOG_ID) DESC;

You could simplify your procedure further. By adding the COUNT to the SELECT list, you can then read the counts out of the cursor and not bother using the V_NO_TRICKS variable.

CURSOR CUR_DOGS (CP_MONTH VARCHAR2) IS
SELECT DR.DOG_NAME, TR.DOG_ID, COUNT (TR.DOG_ID) AS TRICK_COUNT
FROM DOG DR, TRICK TR
-- ... rest of query as before


-- ... later on ...
DBMS_OUTPUT.PUT_LINE (REC_DOGS.DOG_NAME ||' '||REC_DOGS.DOG_ID ||' '|| REC_DOGS.TRICK_COUNT);

Upvotes: 1

Related Questions