| What is the difference between these 2 sql statements [message #378400] |
Tue, 30 December 2008 04:47  |
sathyam2627
Messages: 52
Registered: November 2006
|
Member |
|
|
Hi All,
SQL> ed
Wrote file afiedt.buf
1 SELECT a.sal + trunc(0.04 * avg(b.sal), 2)
2 FROM emp b, Emp a
3* WHERE b.deptno = a.deptno
SQL> /
SELECT a.sal + trunc(0.04 * avg(b.sal), 2)
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> UPDATE emp a
2 SET sal = (
3 SELECT a.sal + trunc(0.04 * avg(b.sal), 2)
4 FROM emp b
5 WHERE b.deptno = a.deptno
6 );
14 rows updated.
I have pasted the 2 sql statments where as 1st sql statement is raising an error by saying a.sal is not a group by column.
1 SELECT a.sal + trunc(0.04 * avg(b.sal), 2)
2 FROM emp b, Emp a
3* WHERE b.deptno = a.deptno
SQL> /
SELECT a.sal + trunc(0.04 * avg(b.sal), 2)
*
ERROR at line 1:
ORA-00937: not a single-group group function
but the 2nd one is executing without raising an error.
SQL> UPDATE emp a
2 SET sal = (
3 SELECT a.sal + trunc(0.04 * avg(b.sal), 2)
4 FROM emp b
5 WHERE b.deptno = a.deptno
6 );
14 rows updated.
Kindly clarify this as i am bit confused with this two statements.
Thanks for your help and interest toward this forum.
|
|
|
|
|
|
| Re: What is the difference between these 2 sql statements [message #378539 is a reply to message #378408] |
Tue, 30 December 2008 22:23  |
sathyam2627
Messages: 52
Registered: November 2006
|
Member |
|
|
Hi Michel,
if we add group by a.sal for the 1st sql statement no doubt it will work.
My question is why group by a.sal is not required in the co-related subquery i.e. 2nd sql statement (Update statement). Why is that update statement is working without group by.
Kindly share your knowledge on this.
Thanks
Regards,
Sathya
|
|
|
|
| Re: What is the difference between these 2 sql statements [message #378544 is a reply to message #378539] |
Tue, 30 December 2008 22:44 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| sathyam2627 wrote on Wed, 31 December 2008 05:23 | My question is why group by a.sal is not required in the co-related subquery i.e. 2nd sql statement (Update statement). Why is that update statement is working without group by.
|
You already answered yourself: it is because it is correlated to the row in table A, so there isno need for grouping.
Maybe its slight rewrite will show it clearer: UPDATE emp a
SET sal = a.sal + (
SELECT trunc(0.04 * avg(b.sal), 2)
FROM emp b
WHERE b.deptno = a.deptno
); |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
