Hello again, 1. The numbers in the selected (REQ_ID) are unique records - I chose 6 that exists in the database. The are the database records that have the old code that is being replaced with the new code.

The Data
OFAAAA|OFAA|
OFAAAB|OFAA|
OFAAAC|OFAA|
OFAAAD|OFAA|
OFAAAE|OFAA|
OFAAAF|OFAA|
OFAAAG|OFAA|
[download]

The code (hopefully making more sense)

#!/usr/bin/perl                                            ### you kno
+w 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                         
+                       
### WILL FIND ALL REQS THEN MAKE CHANGES INSTEAD OF ALL IN ONE LOOP.  
+       
### 2002 03 20                                                        
+       
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,@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,@head_date);                                           
+       
open (CC,"ccchgtab.txt");                                             
+       
        while ($_=<CC>) {                                             
+       
        push (@CC,$_);                                                
+       
        ($old,$new)=split(/\|/,$_);                                   
+                    
        push @old,$old;                                               
+       
        push @new,$new;                                               
+       
        }                                                             
+       
close (CC);                                                           
+              
$i=@old
### Attributes to pass to DBI->connect() to disable automatic         
+       
### error checking                                                    
+       
my %attr = (                                                          
+       
    PrintError => 1,                                                  
+       
    RaiseError => 1,                                                  
+       
);                                                                    
+       
### Perform the connection using the Informix driver                  
+       
my $dbh = DBI->connect( "dbi:Informix:nps\@sumitt_dev","donot","showpa
+ssword"   
,\%attr )or die "Can't connect to database: ", $DBI::errstr, "\n";    
+       
                                                                      
+       
$sth=$dbh->prepare("SELECT req_id, ccode                              
+       
        FROM rtsdet                                                   
+      
        WHERE req_id IN ('48111','15313','45813','15226','48426','7270
+3')    
        ");                                                           
+       
$sth->execute();                                                      
+       
        #print "old cc: $old \n";                                     
+       
        # will dump out all fields $data=$sth->dump_results();        
+       
        #$data=$sth->dump_results();                                  
+       
                while ( ($po, $ccode)  = $sth->fetchrow_array() ){    
+       
                #print " $po $ccode, $head_date \n";                  
+       
                push (@po,$po);                                       
+       
                push (@ccode,$ccode);                                 
+       
                push (@head_date,$head_date);                         
+       
                }                                                     
+       
$sth->finish;                                                         
+       
$sth=$dbh->prepare("UPDATE rtsdet SET ccode=? WHERE req_id=? AND ccode
+=?");                  
        foreach $po(@po) {                                            
+       
                for ($count=0; $count<$i;$count++) {                  
+       
                        $sth->execute($new[$count],$po,$old[$count]); 
+         
                        }                                             
+       
                }                                                     
+                       
$sth->finish;                                                         
+       
$p_str=@po;                                                           
+       
print "# of POs: $p_str \n";
[download]

Again - the problem is the changed codes do the switch - but leave a \ after it

output
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|     
OFAA\ 
|
[download]

So the code does switch OFAAAA to OFAA - but leave a trailing /. I tried using tabs - same problem . Any ideas why? I hope I have been a bit more clear. Thanks for your time!
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.