Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Search (merged 2) - 10g
Multiple Search (merged 2) - 10g [message #411249] Thu, 02 July 2009 14:47 Go to next message
nandan.shahane
Messages: 3
Registered: July 2009
Junior Member
I have a typical requirement which am not able to implement in an effective manner..

> I have to extract (say 15,000) accounts based on a set of conditions..
> For each account extracted, i have extract the SSN of all the account holders (An account can have 4 holders)
> Search the entire ACCOUNT_HOLDER_DETAILS table to search for the above extracted SSN.

The idea is - IDENTIFY IF THE ACCOUNT HOLDER HAS ANY OTHER ACCOUNTS

Below is the table structure..

Table - ACCOUNT_HOLDER_DETAILS (Approx 10 Million records)
BorrowerId SSN Address
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Table - ACCOUNT_HOLDER (Approx 10 Million records)
AccountId Borrowerid
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Table - ACCOUNT (2.5 Million records) => An account can have 4 borrowers
AccountId <..Many other Columns..>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The problem is my Stored procedure will be called from an Online Screen. Any ideas on implementing the above scenario ??

I hope am clear in stating my problem.
Nandan
Re: Multiple Search (merged 2) - 10g [message #411256 is a reply to message #411249] Thu, 02 July 2009 19:23 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>The problem is my Stored procedure will be called from an Online Screen.
Why is calling the Stored Procedure from an "online screen" a problem?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Multiple Search (merged 2) - 10g [message #411272 is a reply to message #411249] Thu, 02 July 2009 23:03 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You just have to create a SELECT statement thta joins the 3 tables.
ACCOUNT <-- AccountId --> ACCOUNT_HOLDER <-- BorrowerId --> ACCOUNT_HOLDER_DETAILS

Regards
Michel
Previous Topic: Missing Numbers
Next Topic: can a procedure identify itself?
Goto Forum:
  


Current Time: Wed Dec 07 08:56:10 CST 2016

Total time taken to generate the page: 0.31575 seconds