| pb with SELECT SUM(X), SUM(Y) where X,Y = result of subquery [message #22267] |
Wed, 02 October 2002 04:54  |
Charlie Gautier
Messages: 2
Registered: October 2002
|
Junior Member |
|
|
The following query :
SELECT SUM(NBANOMALIE2), SUM(NBANOMALIE4)
FROM
(SELECT COUNT(*) AS "NBANOMALIE4" FROM CPN A, ANOCPN B WHERE A.CODENS= '128' AND B.CODANO='4' AND A.NUMFOR = B.NUMFOR) ,
(SELECT COUNT(*) AS "NBANOMALIE2" FROM CPN A, ANOCPN B WHERE A.CODENS= '128' AND B.CODANO='2' AND A.NUMFOR = B.NUMFOR);
is NOT working under Oracle 8.1.7.2 although it works fine under 8.1.6. -
Result given under 8.1.6 :
SUM(NBANOMALIE2) SUM(NBANOMALIE4)
0 1 (correct)
Under 8.1.7.2 :
0 0 (bad)
if query changed to "SELECT SUM(NBANOMALIE4), SUM(NBANOMALIE2)" or if the 2 Select count(*) are swaped:
Under 8.1.7.2 :
1 1 (bad too !)
It seems that under 8.1.7.2 there is a mix between returned values, related with the order of subqueries ?
Has anybody a workaround ? any idea ?
Thanks for any help
Charlie jcgautier@ifrance.com
|
|
|
|
| Re: pb with SELECT SUM(X), SUM(Y) where X,Y = result of subquery [message #22268 is a reply to message #22267] |
Wed, 02 October 2002 05:18  |
Mike T
Messages: 32
Registered: August 2002
|
Member |
|
|
Why not Group By:
SELECT B.CODANO, COUNT(*)
FROM CPN A, ANOCPN B
WHERE A.CODENS= '128' AND B.CODANO IN ('2','4') AND A.NUMFOR = B.NUMFOR
GROUP BY B.CODANO;
If you only want the counts, try Decode:
SELECT DECODE(B.CODANO,'2',1,0) NBANOMALIE2, DECODE(B.CODANO,'4',1,0) NBANOMALIE4
FROM CPN A, ANOCPN B
WHERE A.CODENS= '128' AND B.CODANO IN ('2','4') AND A.NUMFOR = B.NUMFOR;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
