Prepare a query on each table, execute the primary one, and fetch its rows
in the outer loop. As you need information from each secondary table,
execute its query with the appropriate arguments to execute() to select the
necessary rows. This should be reasonably efficient as long as the columns
in the where clauses of the secondary tables are indexed.
Untested example:
my $dbh = DBI -> connect( "dbi:Oracle:$sInst", $sUser, $sPwd,
{ AutoCommit => 0, RaiseError => 1, PrintError => 0 } );
# Pre-prepare both queries and set up returned column bindings
my ( $sA, $sB, $sC, $sD, $sE );
my $sth0 = $dbh -> prepare( 'SELECT a, b, c FROM table1 ORDER BY 1' );
my $sth1 = $dbh -> prepare( 'SELECT d, e FROM table2 WHERE a = ?' );
$sth0 -> execute;
$sth1 -> execute( '' ); # Should be no matching rows
$sth0 -> bind_columns( \( $sA, $sB, $sC ) );
$sth1 -> bind_columns( \( $sD, $sE ) );
# Use the fetched values
while ( $sth0 -> fetch ) {
$sth1 -> execute( $sA );
while ( $sth1 -> fetch ) {
# Do Something with $sA ... $sE
}
# Do Something More with $sA ... $sC plus the results of the inner loop
}
$dbh -> disconnect;
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
----- Original Message -----
From: "Shaozab, Sumera" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, October 10, 2001 13:11
Subject: How to handle duplicate information?
> Hello,
>
> I am joing 4 tables to retrieve data from an oracle database using Perl
DBI.
> Once I recieve the data, I put it in xml format. Table A has 1 to many
> relationship with table B, C, and D. Table D has one to many releationship
> with table B and C. The problem is that every record I recieve I get a
lot
> of duplicate information and I can not use distinct since I am joining
> multiple tables. The following is the select statement:
>
> Select
>
A.R_id,A.F_id,A.pg_cnt,A.r_date,A.name,A.addr1,A.addr2,A.addr3,A.addr4,B.r_n
>
me,B.str1,B.str2,B.city,B.state,B.zip,C.stat,C.y_date,C.x_date,D.p_num,D.a_n
> um from tableA A, tableB B, tableC C, tableD D
>
> where A.b_code = '6'
> and (B.FK_R_id = A.R_id and B.FK_F_id = A.F_id)
> and (C.FK_R_id = A.R_id and C.FK_F_id = A.F_id)
>
>
> and (D.FK_R_id = A.R_id and D.FK_F_id = A.F_id)
> and D.p_num IS NOT NULL
>
>
> I will get thousands of records with duplicate information. Each record
> returned is unique, however
> A.R_id,A.F_id,A.pg_cnt,A.r_date,A.name,A.addr1,A.addr2,A.addr3,A.addr4 is
> repeated for each B.r_nme or each D.p_num,D.a_num etc or
>
A.R_id,A.F_id,A.pg_cnt,A.r_date,A.name,A.addr1,A.addr2,A.addr3,A.addr4,B.r_n
> me,B.str1,B.str2,B.city,B.state,B.zip,C.stat,C.y_date,C.x_date is repeated
> for each D.p_num...
>
> How can I approach this problem so that for each record returned I ignore
> the duplicate information and get take the unique informaton for that
A.R_id
> and A.F_id and take that information and put it in xml format?
>
> Any help is appreciated....
>
> Thanks!!
> Sumera
