Hmmm... Sorry for not including the whole code...here it is. I moved the prepare statement outside the loop. The reason the tables are not joined - the fields I am using are uniquely named - so I do not need to reference them (ie a.field1=b.field2) - do I? Here is the whole code (my apologies for my poor style):

#!/usr/bin/perl -w                                                    
+      
### you know you can probably do this with temp tables in SQL....VVP  
+      
### Description:  change old ccodes with new ones provided            
+      
### Will affect rtsdetail table - ccode field                         
+      
                                                                      
+      
use DBI;            # Load the DBI module                             
+      
use CGI;        # HTML                                                
+      
use strict;     # keep it usable                                      
+      
use CGI qw(:standard);                                                
+      
use CGI::Carp qw( fatalsToBrowser );                                  
+      
                                                                      
+      
                                                                      
+      
###                                                                   
+      
my ($sth);                                                            
+      
my $ccode;                                                            
+      
my(@data,$data);                                                      
+      
my ($p_str,@p_str);                                                   
+      
my $i=0;  # counter                                                   
+      
my ($CC,@CC);                                                         
+      
my ($po,@po);                                                         
+      
my ($old,$new);                                                       
+      
my (@old,@new);                                                       
+      
my $count;                                                            
+      
my $total;                                                            
+      
my $head_date;                                                        
+      
                                                                      
+      
open (CC,"ccchg.txt");                                                
+      
        while ($_=<CC>) {                                             
+      
        push (@CC,$_);                                                
+      
        ($old,$new)=split(/\|/,$_);                                   
+      
        push @old,$old;                                               
+      
        push @new,$new;                                               
+      
        }                                                             
+      
close (POCOL);                                                        
+      
                                                                      
+      
                                                                      
+      
$i=@new;                                                              
+      
#print "$i - EYE \n";                                                 
+      
                                                                      
+      
### Attributes to pass to DBI->connect() to disable automatic         
+      
### error checking                                                    
+      
my %attr = (                                                          
+      
    PrintError => 0,                                                  
+      
    RaiseError => 0,                                                  
+      
);                                                                    
+      
                                                                      
+      
### Perform the connection using the Informix driver                  
+      
my $dbh = DBI->connect( "dbi:Informix:nps\@sumitt_aaa","netsite","Ppol
+0699" 
 ,\%attr )or die "Can't connect to database: ", $DBI::errstr, "\n";   
+      
                                                                      
+      
$sth=$dbh->prepare("select det_number,det_ccode,head_date from podetai
+l a,po
er b where det_number=head_number and head_date>'2002/01/01'");       
+      
                                                                      
+      
$sth->execute();                                                      
+      
# will dump out all fields $data=$sth->dump_results();                
+      
#$data=$sth->dump_results();                                          
+      
                                                                      
+      
                while ( ($po, $ccode,$head_date)  = $sth->fetchrow_arr
+ay() )
                #print " $po $ccode, $head_date \n";                  
+      
                push (@po,$po);                                       
+      
                                                                      
+      
        }                                                             
+      
$sth->finish;                                                         
+      
                                                                      
+      
$p_str=@po;                                                           
+      
print "# of POs: $p_str \n";                                          
+      
        $sth=$dbh->prepare("select a.det_number from podetail a, pohea
+der b 
e det_ccode=? and head_date>'2002/01/01' and a.det_number=?");        
+      
foreach $po(@po) {                                                    
+      
        for ($count=0; $count<=$i;$count++) {                         
+      
        #print "old cc: $old[$count], PO checked: $po \n";            
+      
        $sth->execute($old[$count],$po);                              
+      
        $p_str=$sth->fetchrow_array();                                
+      
                if ($p_str) {                                         
+      
                push (@p_str,$p_str);                                 
+      
                print "$p_str \n";                                    
+      
                }                                                     
+      
$sth->finish;                                                         
+      
        }                                                             
+      
}                                                                     
+      
$total=@p_str;                                                        
+      
print "Total: $total  AND @p_str \n";                                 
+      
$total=@po;                                                           
+      
print "POs used: $total \n";
[download]

Thanks, V He who laughs last, doesn't get the joke.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.