| ORA-00937: not a single-group group function [message #148122] |
Tue, 22 November 2005 19:30  |
kuti36
Messages: 2
Registered: November 2005
|
Junior Member |
|
|
Here is a function I have created....
SQL> CREATE OR REPLACE FUNCTION nights_disc_sf
2 (booking_id IN CHAR)
3 RETURN NUMBER
4 IS
5 roomNo NUMBER(2);
6 lv_noNigh_num NUMBER(2);
7 lv_room_price NUMBER(5,2);
8 lv_booking_disc NUMBER(5,2);
9 BEGIN
10 SELECT count(r_room_no),b.noOfNights, rt.price
11 INTO roomNo,lv_noNigh_num, lv_room_price
12 FROM booking b, room r, booking_room br, room_type rt
13 WHERE booking_id = br.b_booking_id
14 AND br.r_room_no = r.room_no
15 AND r.fk1_room_type_code = rt.room_type_code;
16
17 IF lv_noNigh_num > 3 THEN
18 lv_booking_disc := lv_noNigh_num * lv_room_price/0.1;
19
20 ELSIF lv_noNigh_num > 6 THEN
21 lv_booking_disc := lv_noNigh_num * lv_room_price/0.2;
22
23 ELSE lv_booking_disc := lv_noNigh_num * lv_room_price;
24 END IF;
25 RETURN lv_booking_disc;
26 END;
27 /
Function created.
I am basically trying to calculate the bill for a customer and adding on discounts where applicable.
Here is my problem
SQL> SELECT booking_id,nights_disc_sf(booking_id)
2 FROM BOOKING
3 WHERE booking_id = 1;
SELECT booking_id,nights_disc_sf(booking_id)
*
ERROR at line 1:
ORA-00937: not a single-group group function
ORA-06512: at "C3053014.NIGHTS_DISC_SF", line 10
When i try to run the function it kicks up this error! I had never seen this error before so I googled it.
This is what i got!
| Quote: |
ORA-00937: not a single-group group function
Cause: A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.
Action: Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed.
|
But i don't understand this problem! Please help me by explaing where I have gone wrong. Thanks.
|
|
|
|
| Re: ORA-00937: not a single-group group function [message #148124 is a reply to message #148122] |
Tue, 22 November 2005 20:10  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You need a GROUP BY clause in the SQL in your function. Cut the SQL out of the function and get it running in SQL*Plus. When it works, paste it back into the function.
This seems to be a short-coming in the PL/SQL compiler. Whilst PL/SQL will parse the SQL for syntax and permission errors, it seems that there are a class of errors that are not checked until run-time.
ie. Just because the function compiles doesn't mean it's right.
I learned something here: I would have bet that ORA-00937 would be detected at compile-time.
_____________
Ross Leishman
|
|
|
|
|
|
| Re: ORA-00937: not a single-group group function [message #148136 is a reply to message #148122] |
Tue, 22 November 2005 21:49 |
ndefontenay
Messages: 14
Registered: November 2005
Location: Thailand
|
Junior Member |
|
|
The problem is very simple.
You are trying to perform a count without using the GROUP BY function.
This problem comes from the COUNT() function you are using. When you use functions like COUNT(), SUM(), you need to group by all the members included in your SELECT clause
So your answer would be:
SELECT count(r_room_no),b.noOfNights, rt.price
11 INTO roomNo,lv_noNigh_num, lv_room_price
12 FROM booking b, room r, booking_room br, room_type rt
13 WHERE booking_id = br.b_booking_id
14 AND br.r_room_no = r.room_no
15 AND r.fk1_room_type_code = rt.room_type_code
[COLOR=red] 16 GROUP BY r_room_no,b.noOfNights, rt.price[/COLOR]
What this means is that the results will count a r_room_no for every similar No of nights and price couples.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
