aspose file tools*
The moose likes Oracle/OAS and the fly likes Query gives the wrong data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Query gives the wrong data" Watch "Query gives the wrong data" New topic
Author

Query gives the wrong data

Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
I have 2 tables, TransportPayment and TuitionPayment, both of them has these 3 columns : payment, due,discount. I want to get sum of payments, max of due and sum of discount of both the tables in a single query.

This is the query i wrote :



But, this query gives me the wrong datas. What correction does this query needs?


kunal
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18564
    
    8

That's impossible to say without knowing something about the problem. Right now, all we know is that you don't like the answer you get. But without knowing what the right answer is, what the wrong answer you're getting is, and what the differences are, there's nothing to be said.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Thanks for your reply Paul

I wrote a separate query for Transport and Tuition Payment (I want the combination of 2)

Query for TuitionPayment


Gets me :
Enrollmentno =1004
fullname = Chris
Tuition fee = 20000
sum(tp.payment) = 5000
sum(tp.discount) = 5000



Query for TransportPayment

This Gets me
Enrollmentno =1004
fullname = Chris
Transport fee = 5000
sum(tp.payment) = 1000
sum(tp.discount) = 0

When i want both the table details as a single row. The query i posted gives this output (specific to enrollmentno 1004)
Enrollmentno =1004
fullname = Chris
Tuition fee = 20000
Tuition Payment = 7000 //wrong data
Tuition Discount = 10000 //wrong data
Transport fee = 5000
Transport payment = 3000 //wrong data
Transport discount = 0 //wrong data
Christophe Verré
Sheriff

Joined: Nov 24, 2005
Posts: 14687
    
  16

Make two queries : an inner query joining both tables and getting all necessary columns. An outer query grouping the results of the inner query.


[My Blog]
All roads lead to JavaRanch
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
One important thing i need to tell, TuitionPayment table contains 3 records with enrollmentNo 1004, where as Transportpayment table contains 2 records with enrollmentno 1004. I think that's the reason why it gets incorrect data
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Yes, that's the reason. Some rows in your query appear multiple times in the result because of the join. Christophe's advice will avoid that.
Kunal Lakhani
Ranch Hand

Joined: Jun 05, 2010
Posts: 622
Thanks Martin for your reply
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Query gives the wrong data