Comments below.
--
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]>; "Michael A Chase" <[EMAIL PROTECTED]>
Sent: Tuesday, October 16, 2001 15:47
Subject: RE: How to handle duplicate information?
> Hi Michael,
>
> Thank you for your response!
>
> Please help me understand clearly:
>
> What does $sth1 -> execute( '' ) do? Why is it being done two times, once
> without a variable and once with a varible in the while loop?
This execute() call executes the query with an empty string as an argument.
Oracle's SQL engine treats empty strings the same as NULL. Ordinarily, I'd
use undef instead, but in some cases trailing undefs get stripped from
argument lists which would result in an error in this case since execute()
knows how many parameters it needs to match the placeholders.
I wanted to execute() the query to make sure DBI knew how many columns were
being returned so bind_columns() would work correctly. The discussion of
bind_columns() in the DBI manual (perldoc DBI) explains this in more detail.
> Since I am querying against 5 tables, should I do 5 seperate queries with
> five prepare,execute,and fetch? Like the following you have suggested:
Yes. You are doing about what I'd do.
> > my $sth0 = $dbh -> prepare( 'SELECT a, b, c FROM table1 ORDER BY 1' );
> > my $sth1 = $dbh -> prepare( 'SELECT d, e FROM table2 WHERE a = ? ');
> > my $sth2 = $dbh -> prepare( 'SELECT f, g, h FROM table3 WHERE d = ? ');
> > my $sth3 = $dbh -> prepare( 'SELECT i, j, k FROM table4 WHERE f = ? ');
> > my $sth4 = $dbh -> prepare( 'SELECT l, m, n FROM table5 WHERE j = ?' );
> >
> > $sth0 -> execute;
> > $sth1 -> execute( '' );
> > $sth2 -> execute( '' );
> $sth3 -> execute( '' );
> $sth4 -> execute( '' );
> > $sth0 -> bind_columns( \( $sA, $sB, $sC ) );
> > $sth1 -> bind_columns( \( $sD, $sE ) );
> > $sth2 -> bind_columns( \( $sf, $sg, $sh ) );
> $sth3 -> bind_columns( \( $si, $sj, $sk ) );
> $sth4 -> bind_columns( \( $sl, $sm, $sn ) );
>
>
> > # Use the fetched values
> > while ( $sth0 -> fetch ) {
> > $sth1 -> execute( $sA );
> > while ( $sth1 -> fetch ) {
> > # Do Something with $sA ... $sE
> > }
> >
> > $sth2 -> execute( $sD );
> > while ( $sth2 -> fetch ) {
> > # Do Something with $sf ... $sh }
> $sth3 -> execute( $sg );
> > while ( $sth -> fetch ) {
> > # Do Something with here
> > }
> }
>
> $sth4 -> execute( $sk);
> > while ( $sth4 -> fetch ) {
> > # Do Something with $sl ... $sn
> > }
> >
> > }
> > $dbh -> disconnect;
> >
> Do you know if that if there is a performance difference if I join all 5
> tables and do one prepare/execute/fetch or if If I do 5 seperate
> prepare/execute/fetch.
There probably will be a performance benefit to joining tables in the SQL
instead of executing separate queries. You'd have to experiment to see how
useful it is for you. One disadvantage to joining multiple tables is that
it is sometimes difficult to control how many times you see the same values.
> > ----------
> > From: Michael A Chase[SMTP:[EMAIL PROTECTED]]
> > Sent: Wednesday, October 10, 2001 4:44 PM
> > To: Shaozab, Sumera; [EMAIL PROTECTED]
> > Subject: Re: How to handle duplicate information?
> >
> > 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
> >
> >
