Home » SQL & PL/SQL » SQL & PL/SQL » filter invalid amount records
filter invalid amount records [message #332166] Mon, 07 July 2008 14:10 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

I have these 2 tables ...

table1

loan_id  acct_nbr  exp_acct  amount
123        a1234     8765     50.00
156        a3245     9809     100.00
876        a7564     0975     500.00

table2
loan_id  amount
123      10.00
123      10.00
123      10.00
123      10.00
123      10.00
123      140.00
876      600.00
876      200.00
876      300.00



The table2 is the detail table of table1, but containing some records of invalid amount. i need to select detail records from table2 based on this rule...

Like loan_id = 876 containing 3 amounts 600 , 200 and 300. so i need to select that loan id, if i add their amount which should be equal to the amount of table1 of same loan_id.

The query result from table2 should be Something like this..

loan_id  amount
123      10.00
123      10.00
123      10.00
123      10.00
123      10.00
876      200.00
876      300.00



I am not looking for detail answer. any clue or hint would be appreciated..
Re: filter invalid amount records [message #332171 is a reply to message #332166] Mon, 07 July 2008 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what happens if instead of 600 you had 500?
And what about loan_id 156?

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

Regards
Michel
Re: filter invalid amount records [message #332173 is a reply to message #332171] Mon, 07 July 2008 14:27 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

>>'And what happens if instead of 600 you had 500?'
If the amount is also exactly matched with the loan_id .. that is ideal situation.. but if not, then take the sum of amount and match against the table1 of the same loan_id..

>>And what about loan_id 156?
I am selecting only those loan_id's from table2 which exists in table1 having the same amount or end up getting the amount matched, after adding them....

Appreciate your valuable time ...
Re: filter invalid amount records [message #332175 is a reply to message #332173] Mon, 07 July 2008 14:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now if instead of 600, you have 2 rows 100 and 400 in addition to 200 and 300?

Still waiting for a complete test case.

Regards
Michel
Re: filter invalid amount records [message #332179 is a reply to message #332175] Mon, 07 July 2008 14:50 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Ok here is some test cases...

select only these records from table2

1)Firstly, if the loan_id and the amount of the table2 matches with the loan_id and amount of table1. (ideal situation)

2) secondly, if the only loan_id of the table2 matched against table1, then sum the amount of same loan_id and try to make sure it matches the amount of table1. but if there is more than one match, then pickup first match only.
Re: filter invalid amount records [message #332181 is a reply to message #332166] Mon, 07 July 2008 14:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

The post above is NOT an acceptable or valid test case.
Read Posting Guideline & follow them if you desire & expect additional assistance.

[Updated on: Mon, 07 July 2008 15:25] by Moderator

Report message to a moderator

Re: filter invalid amount records [message #332255 is a reply to message #332179] Tue, 08 July 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then pickup first match only.

Which one is first?

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

Regards
Michel
Re: filter invalid amount records [message #332734 is a reply to message #332255] Wed, 09 July 2008 09:02 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

I do apologize for unable in explaining my problem, and waisting your valuable time. I am trying once more to explain my problem..
table1
loan_nbr   amount
123         100.00
1234        130.00

table2
loan_nbr    amount
123         25.00
123         25.00
123         25.00
123         25.00
123         1234.43

please just give me a small hint, to select only those loan_nbr from table2 whose sum amount is exactly equal to the matching loan_nbr of table1. i am just looking any hint to create a logic only for this situtaion, which i tried to mentioned above in the table..

Thanks and appreciate your time.
Re: filter invalid amount records [message #332737 is a reply to message #332734] Wed, 09 July 2008 09:14 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your requirements are not complete, so we can't answer.
Once again post a test case we can work with.

Regards
Michel
Previous Topic: using SYSDATE (code interpretation)
Next Topic: sql server query to oracle 10g query
Goto Forum:
  


Current Time: Sat Dec 10 16:33:21 CST 2016

Total time taken to generate the page: 0.09158 seconds