PERL get the value for query

I have the attached perl script and i want one addition to the existing script.
#!/usr/bin/perl

use 5.006;
use strict;
use warnings;
use Pod::Usage;
use Getopt::Long;
use Cwd;
use DateTime;
use DBD::mysql;
use DBI;

my $helpme = 0;
my $man = 0;
my $inputFileName = 'C:\\temp\\testfile.txt';
my $outputFileName = 'C:\\temp\\outfile.txt';
my $req_line_number = 2;
my $count = 1;
my $dsn = 'DBI:ODBC:Driver={SQL Server}';
my $host = 'testhost';
my $database = 'testdb';
my $user = 'user1';
my $auth = 'password';
my $linecount = 0;

GetOptions('help' => \$helpme, 'man' => \$man, 'infile=s' => \$inputFileName, 'outfile=s' => \$outputFileName) or pod2usage(2);

pod2usage(1) if $helpme;
pod2usage(-verbose => 2) if $man;

die "No input file name specified!" if !$inputFileName;
die "No output file name specified!" if !$outputFileName;
my $dbh = DBI->connect("$dsn;Server=$host;Database=$database", $user, $auth,{ RaiseError => 1, AutoCommit => 1}) || die "Database connection not made: $DBI::errstr"; 

open(INFILE, "<", $inputFileName) || die "Could not open input file!";
open(OUTFILE, ">", $outputFileName) || die "Could not open/create output file!";

my @r; 
my $data = '';
while (<INFILE>) {
 $_ =~ s/\r|\n//g; 
$data = join('',$data,$_); 
} 
$data =~s/(?<=REF\*DOL\*)(\d+)~/@{[@r=$dbh->selectrow_array( "select DISTINCT member_id    
from relation as cr
join Contacts as c on c.id = cr.id and c.status = cr.status  
join Coverage as ec on ec.id = cr.id and ec.status = cr.status
where cr.status = 'A'  and ec.member_id = '$1'")]}~REF*AP*@r~/g;
$_ = $data;
print $linecount = (/(~ST\*.*?~SE\*)/)[0] =~ tr/~/~/; 
$data =~ s/SE\*\d+?\*(\d+?)~/SE\*$linecount\*$1~/;
print OUTFILE $data; 

close INFILE;
close OUTFILE;

Open in new window


Here is the file that i am working on

ISDFFS*00*          *00*          *30*7984581235      *ZZ*AKLSJFHADSJF   *624654*1805*^*00501*3465465*1*T*:~
RS*GS*5145454121*AKLSJFHADSJF*624654*1805*7984581235*X*005010X306~
ST*820*7984581235*005010X306~
SDF*I*0*C*NON************624654~
SDFS*3*99999~
SDF*582****RD8*20141201-20141231~
SWRE*1121326~
NM1*IL*1*BOURNE*BOND*L***C1*100000257~
REF*48*77777AA000000001~
REF*PP*77777AA000000001~
DEF*PF*LKJHAASDA*~
REF*DOL*100000037~
RSRRE*RER*ERER**-2132154021.14~
DRERF*582****RD8*20150701-20150731~
NM1*IL*1*DENVER*NEWYORK****C1*100000089~
REF*48*77777AA000000002~
REF*PP*77777AA000000002~
DEF*PF*LKJHA*~
REF*DOL*100000257~
RSRRE*RER*ERER**-2132154021.14~
DRERF*582****RD8*20150701-20150731~
SE*8*7984581235~
RT*1*7984581235~
IAS*1*7984581235~


Now if i use the query in the script that i had written it works for few values
but for values like 100000257 there are multiple results so I want to filter the query little more
so i thought to use the value 77777AA000000002 from the below line
REF*PP*77777AA000000002~

So that i can update my query as below

select DISTINCT member_id    
from relation as cr
join Contacts as c on c.id = cr.id and c.status = cr.status  
join Coverage as ec on ec.id = cr.id and ec.status = cr.status
where cr.status = 'A'  and ec.member_id = '$1' and id = '77777AA000000002'

The confusing part is there are two lines that starts with
REF*PP*
but if you observe the file the file has two sections
SECTION 1:
NM1*IL*1*BOURNE*BOND*L***C1*100000257~
REF*48*77777AA000000001~
REF*PP*77777AA000000001~
DEF*PF*LKJHAASDA*~
REF*DOL*100000037~
RSRRE*RER*ERER**-2132154021.14~
DRERF*582****RD8*20150701-20150731~

SECTION 2:
NM1*IL*1*DENVER*NEWYORK****C1*100000089~
REF*48*77777AA000000002~
REF*PP*77777AA000000002~
DEF*PF*LKJHA*~
REF*DOL*100000257~
RSRRE*RER*ERER**-2132154021.14~
DRERF*582****RD8*20150701-20150731~

so if i am updating the below line  
REF*DOL*100000257~
then i want to use the corresponding section id value 77777AA000000002 but not 77777AA000000001

so is it possible to do that.

Thanks,
shragiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ozoCommented:
If every REF*PP* is followed by a REF*DOL*:

s/(REF\*PP\*(\w+).*?REF\*DOL\*)(\d+)~/$1@{[@r=$dbh->selectrow_array( "
select DISTINCT member_id    
from relation as cr
join Contacts as c on c.id = cr.id and c.status = cr.status  
join Coverage as ec on ec.id = cr.id and ec.status = cr.status
where cr.status = 'A'  and ec.member_id = '$3' and id = '$1'
")]}~REF*AP*@r~/g;
0
shragiAuthor Commented:
thanks ozo.
I printed $1 and $3 and i got the below values...so something is off here

$1 = REF*PP*77777AA000000002~DEF*PF*LKJHA*~REF*DOL*
$3 = "100000257"

so how can i get 77777AA000000002 as $1 value
0
ozoCommented:
Sorry, that should have been
s/(REF\*PP\*(\w+).*?REF\*DOL\*)(\d+)~/$1@{[@r=$dbh->selectrow_array( "
select DISTINCT member_id    
from relation as cr
join Contacts as c on c.id = cr.id and c.status = cr.status  
join Coverage as ec on ec.id = cr.id and ec.status = cr.status
where cr.status = 'A'  and ec.member_id = '$3' and id = '$2'
")]}~REF*AP*@r~/g;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shragiAuthor Commented:
thanks ozo that worked :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.