Home » SQL & PL/SQL » SQL & PL/SQL » query fetching wrong results (oracle10g)
query fetching wrong results [message #431921] Fri, 20 November 2009 01:55 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
I have a data like this;i have written query which should have count of detailsequences are 3 and sum of outstanding amount <> 0 for the same empid and headerseq and should have same exchange rate.but query fetching wrong results.
SELECT em.empid, em.main_seq
  FROM EMP em
 WHERE 
    EXISTS
 (SELECT 'X'
          FROM EMP E
         WHERE em.empid =  E.empid
           AND em.main_seq =  E.main_seq
           AND em.exchangerate =  E.exchangerate
         GROUP BY E.empid, E.main_seq, E.exchangerate
        HAVING SUM(NVL(E.amount, 0)) <> 0 AND COUNT(E.deatilseq) = 3
        );


empid	main_seq	deatilseq	amount	exchangerate
3333	7	1	  -130.60	             67.660000000
3333	7	2	0.00	             67.660000000
3333	7	3	3305.00	              67.660000000
3333	7	4	0.00	             65.680000000
44444	8	1	1	1
44444	8	2	-1	1
44444	8	3	0	1
55555	2	1	4	1
55555	2	2	24	1
55555	2	3	24	1

final result should fetch only empid=55555 record;

55555 2

but itsb also fetching 3333 record.

[Updated on: Fri, 20 November 2009 02:00] by Moderator

Report message to a moderator

Re: query fetching wrong results [message #431922 is a reply to message #431921] Fri, 20 November 2009 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session and align the columns in result.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Fri, 20 November 2009 02:02]

Report message to a moderator

Re: query fetching wrong results [message #431923 is a reply to message #431921] Fri, 20 November 2009 02:04 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Can you read it?
Anyway it will give 3333 also. Check your sql again.
First three rows the exchange rate is same and total is not equal to zero.
What else you want?

By
Vamsi
Re: query fetching wrong results [message #432016 is a reply to message #431921] Fri, 20 November 2009 07:27 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
I think i should change the query like this;

SELECT em.empid, em.main_seq
FROM EMP em
WHERE
1=
(SELECT COUNT( DISTINCT E.exchangerate)
FROM EMP E
WHERE em.empid = e.empid
AND em.main_seq = E.MAIN_SEQ
GROUP BY E.empid, E.main_seq
HAVING SUM(NVL(E.amount, 0)) <> 0 AND COUNT(E.deatilseq) = 3
);
Re: query fetching wrong results [message #432017 is a reply to message #432016] Fri, 20 November 2009 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you didn't read the OraFAQ Forum Guide, so please do it and format your post.

Regards
Michel
Re: query fetching wrong results [message #432020 is a reply to message #431921] Fri, 20 November 2009 07:51 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
formatted.now please check the query.

SELECT em.empid, em.main_seq
FROM EMP em
WHERE 
1= 
(SELECT COUNT( DISTINCT E.exchangerate)
FROM EMP E
WHERE em.empid = e.empid
AND em.main_seq = E.MAIN_SEQ
GROUP BY E.empid, E.main_seq
HAVING SUM(NVL(E.amount, 0)) <> 0 AND COUNT(E.deatilseq) = 3
); 

Re: query fetching wrong results [message #432021 is a reply to message #432020] Fri, 20 November 2009 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Formated also means indented not just code tags.

Regards
Michel
Re: query fetching wrong results [message #432026 is a reply to message #432020] Fri, 20 November 2009 08:15 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
What exactly is it that you want checked? that the query returns the expected results? Surely you would be the best person to check that.
Previous Topic: Calulate the Eaxct Hours, Minutes second
Next Topic: How to get a Key based on exact matching value combination
Goto Forum:
  


Current Time: Fri Dec 02 14:32:37 CST 2016

Total time taken to generate the page: 0.12537 seconds