Thanks Andy,
eq instead of == works like a charm. I don't know why I always use numeric operators
>>> "De Simone, Andrew (CAP, FGI)" <[EMAIL PROTECTED]> 07/16/01 03:31PM >>>
Greg,
Try changing your comparisons, for example: ($za01 == "QS") to ($za01 eq 'QS')
or a pattern match, like ($za01 =~ m/QS/). This is more of a PERL issue than DBI
related to
it's use of strings.
Andy
-----Original Message-----
From: Greg Wardawy [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 16, 2001 3:22 PM
To: [EMAIL PROTECTED]
Subject: inserting data into different columns
depending on the value of the variable.
Hello all,
I'm having problems trying to insert data into different columns depending on the
value of the variable.
Here is the situation:
if the value of $za01 = "QA" I need the value of $sdq[$i+1] to be written into the
curr_invent column but if $za01 = "QS" it should be written into the net_sold column.
I don't
know why I'm getting all values of $sdq[$i+1] only in the curr_invent column and
nothing in the net_sold. I'm also not sure if I need two different statement handles
or it can be
done using just one.
Thanks a lot for your time and help.
Greg
Here's my script:
##################################
#!perl
use strict;
use Date::Format;
use DBI;
use diagnostics;
my $dir = "G:\\apps\\quickedi\\ib";
my $recv_file = "recv.1";
my $elite_file = "item_upc.txt";
print "Poczekaj chwile..............\n";
open(ITEMUPC, "> $dir/$elite_file ") || die "can't create $dir/$elite_file:
$!";
my %upc_itemlist;
my %item_upclist;
my ($upc_num,$item_num);
my $DSN = "Elite";
my $dbh1 = DBI->connect ("dbi:ODBC:$DSN", "", "",
{RaiseError => 1, AutoCommit => 0});
my $SQL = $dbh1->prepare("select upc, item_num from item where org_code = '01' and
item_num = report_item_num and memo_1 <> 'TITLE' and upc is not null and upc <> 'NONE'
order by
upc");
$SQL->execute;
while ( my @row = $SQL->fetchrow_array) {
my($upc_num,$item,$price) = @row;
$upc_num=~ s/\s+//g;
($item =$item) =~ s/\s+$//;
print ITEMUPC "$upc_num,$item\n";
}
$dbh1->rollback;
$dbh1->disconnect;
close ITEMUPC;
open(CHECK, "< $dir/$elite_file");
while (<CHECK>) {
chomp;
($upc_num, $item_num) = split /\s*,\s*/;
$upc_itemlist{$upc_num} = $item_num;
}
close CHECK;
no warnings 'uninitialized';
my $dbh = DBI->connect( "dbi:ODBC:EDI Data", "", "" )or die "Can't connect to ODBC
database: $DBI::errstr\n";
my ($count,$customer,$sent,$pad_num,$upc,$za01,$xq02,$xq03,$inventory,$sold);
my @names1 =
qq(customer,upc_num,item_num,store_num,curr_invent,beginning_date,ending_date,sent);
my @names2 =
qq(customer,upc_num,item_num,store_num,net_sold,beginning_date,ending_date,sent);
my $sth1 = $dbh->prepare("INSERT INTO edi_pad_test (@names1) VALUES
(?,?,?,?,?,?,?,?)");
my $sth2 = $dbh->prepare("INSERT INTO edi_pad_test (@names2) VALUES
(?,?,?,?,?,?,?,?)");
open(RECV, "< $dir/$recv_file") || die "can't open $recv_file: $_!";
while (<RECV>) {
s/.$//;
chomp;
my @fields = split /\</;
if (/^ISA<00< <00< <12<2017781300/) {
$customer = "3";
} elsif (/^GS<PD<2017781300/) {
$sent = $fields[4];
} elsif (/^ST<852/) {
$pad_num = $fields[2];
} elsif (/^XQ/) {
$xq02 = $fields[2]; #Begining Date Range
$xq03 = $fields[3]; #Ending Date Range
} elsif (/^N9/) {
#ignore
} elsif (/^LIN/) {
$upc = $fields[3]; #UPC number
} elsif (/^ZA/) {
$za01 = $fields[1]; #QA = Current Inventory Qty, QS = Net Qty Sold
} elsif (/^SDQ/) {
$upc_itemlist{$upc} = "NO_ELITE_ITEM_NUM_FOR_LNT_UPC" unless
exists($upc_itemlist{$upc});
my @sdq = split/\</;
if ($za01 == "QA") {
# the data should be inserted into the curr_invent column
for (my $i=3;$i<=21;$i+=2) {
if (defined $pad_num) { # because other transactions also have
a SDQ lines
if (defined $sdq[$i]) { # some SDQ lines have less then 22
fields
$inventory = $sdq[$i + 1];
print
"Processing:\t$pad_num,$upc,$sdq[$i],$za01.....$inventory\n";
$sth1->execute($customer,$upc,$upc_itemlist{$upc},$sdq[$i],$inventory,$xq02,$xq03,$sent)
or die "can't execute SQL statement: $DBI::errstr\n";
} else {
#ignore
}
} else {
#ignore
}
}
} elsif ($za01 == "QS") {
# the data should be inserted into the net_sold column
for (my $i=3;$i<=21;$i+=2) {
if (defined $pad_num) { # because other transactions also have
a SDQ lines
if (defined $sdq[$i]) { # some SDQ lines have less then 22
fields
$sold = $sdq[$i + 1];
print
"Processing:\t$pad_num,$upc,$sdq[$i],$za01.....$sold\n";
$sth2->execute($customer,$upc,$upc_itemlist{$upc},$sdq[$i],$sold,$xq02,$xq03,$sent) or
die "can't execute SQL statement: $DBI::errstr\n";
} else {
#ignore
}
} else {
#ignore
}
}
} else {
#ignore
}
} elsif (/^CTT/) {
#ignore
} elsif (/^SE/) {
#ignore
} elsif (/^GE/) {
#ignore
} elsif (/^IEA/) {
#ignore
} else {
next;
# print "Unrecognized: ", substr ($_, 0, 10), "\n";
}
}
close RECV;
$dbh->disconnect;
####################################
and the part of my data file:
ISA<00< <00< <12<2017781300 <12<7084061572
<010716<0601<U<00401<000000630<0<P<:
GS<PD<2017781300<7084061572<20010716<0601<630<X<004010VICS
ST<852<6300001
XQ<H<20010708<20010714
N9<IA<1982
LIN<<UP<009281020035
ZA<QA
SDQ<EA<92<129<5<183<23<194<6<288<14<302<3<305<3<320<29<342<1<347<-1<350<-13
SDQ<EA<92<354<15<355<1<358<7<359<7<360<8<362<13<363<2<364<7<365<11<367<3
SDQ<EA<92<368<2<371<5<372<1<373<1<374<4<376<10<377<5<378<-2<379<2<382<1
SDQ<EA<92<383<-2<384<7<386<6<388<5<391<17<392<9<394<25<395<6<398<-1<399<1
SDQ<EA<92<400<14<402<-3<403<-13<405<8<408<17<410<7<411<14<412<9<418<2<419<5
SDQ<EA<92<421<3<422<19<423<-4<424<12<425<19<428<14<429<-7<430<1<438<-1<439<6
SDQ<EA<92<441<8<442<13<443<2<444<-13<445<14<446<7<447<7<448<13<449<4<450<-2
SDQ<EA<92<452<-2<453<1<454<24<458<-3<459<8<461<13<462<13<463<17<464<12<467<1
SDQ<EA<92<468<-9<469<4<470<-1<472<7<474<7<476<17<477<1<478<2<480<23<482<16
SDQ<EA<92<483<12<485<-25<488<-1<489<15<490<6<491<27<495<27<497<16<499<5<500<22
SDQ<EA<92<501<-10<502<-1<503<28<504<-1<505<13<506<-7<507<-2<509<4<510<1<511<-10
SDQ<EA<92<512<6<513<-6<516<21<517<7<518<-1<519<7<520<19<521<4<522<-13<527<4
SDQ<EA<92<529<16<533<13<534<6<535<7<538<-9<539<1<541<23<543<15<544<7<546<3
SDQ<EA<92<549<3<550<-18<553<-1<555<18<561<8<563<4<564<10<571<23<572<1<573<25
SDQ<EA<92<575<2<576<-10<580<7<584<-19<585<22<586<45<587<25<588<6<589<10<590<1
SDQ<EA<92<592<23<593<8<594<1<599<1<602<1<603<13<610<-14<612<-6<616<1<618<9
SDQ<EA<92<620<11<623<16<624<9<625<7<628<16<630<10<633<14<635<20<636<10<637<21
SDQ<EA<92<638<1<640<10<641<25<643<10<646<1<649<30<651<1<656<-2<661<4<663<-10
SDQ<EA<92<664<2<666<15<669<-31<673<8<684<10<688<5
ZA<QS
SDQ<EA<92<371<1<381<1<428<1<478<1<480<1<495<1<497<1<521<1<593<1<630<1
LIN<<UP<009281021315
ZA<QA
SDQ<EA<92<305<31<320<-3<338<2<346<31<348<94<354<30<355<-2<360<35<365<-1<373<102
SDQ<EA<92<374<9<377<77<379<-9<381<49<382<2<386<54<390<87<392<84<394<-1<399<7
SDQ<EA<92<402<-5<405<5<409<73<421<88<425<-3<436<1<437<-4<441<56<462<209<468<1
SDQ<EA<92<480<9<483<-3<488<-2<494<1<495<16<502<-1<515<82<523<1<526<-1<527<101
SDQ<EA<92<536<9<552<92<555<2<564<-2<569<-1<574<57<581<108<586<10<595<-6<601<16
SDQ<EA<92<609<2<616<30<632<1<635<-2<646<4<654<-2
ZA<QS
SDQ<EA<92<348<1<354<1<390<1<394<1<441<1<483<1<488<1<569<1<574<1<635<1
CTT<702
SE<9200<6300001
GE<1<630
IEA<1<000000630
