MIXORD077768
MIXORD077769
[download]

#!/bin/perl

#use strict;
#allows use of the copy and move commands for files
use File::Copy;
#allows for parsing words
use Text::ParseWords;
use Win32::ODBC;
use Time::Local;
use POSIX;
use Data::Dumper;
#for emailing errors
use Net::SMTP;

# oracle uid and pw
my $user= 'test';
my $passwd = 'test';


#######Prod Variables
my $outbox = "D:\\scripts\\outbox\\";
my $archive = "D:\\scripts\\archive\\";
my $log = "D:\\scripts\\log\\log.txt";
my $partlist = "D:\\scripts\\partlist.txt";

#put view for in an array to loop through each db
@datasources = ("my_datasource");

       
foreach $datasource (@datasources) {
    
#open logfile
open(LOG_FILE, ">>$log")||die "Can't open file: $!";
print LOG_FILE ("begin\n");
        
#initiate DB
($Db) = new Win32::ODBC("DSN=$datasource;UID=$user;PWD=$passwd");
if (!$Db){
print LOG_FILE ("Could not open connection to DSN because of [$!]\n");
die "Could not open connection to DSN because of [$!]";
print LOG_FILE ($Db->Error()."\n");
print ($Db->Error()."\n");
}
## populate the string that will be used for the SQL query 
my $statement;
$statement = "SELECT DISTINCT a.ordnum ORDNUM, a.shipid SHIPID, a.date
+ DATE";
$statement = $statement." FROM new_view a";
$statement = $statement." order by ORDNUM";

$Db->Sql($statement);


##Step through each returned row
while ($Db ->FetchRow()) {
    my (%dataRow) = $Db->DataHash();
        
    my $ordnum = $dataRow{'ORDNUM'};
    my $shipid = $dataRow{'SHIPID'};
    my $date = $dataRow{'DATE'};
    #set custmoer variable for 
    if ($shipid eq '12345678') {
        $customer='CUSTOMER1';
    }
    else {
        $customer='OTHERS';
    }

    #put date in zulu format
    my $date_z = substr($dataRow{'DATE'},0,10)."T".substr($dataRow{'DA
+TE'},-8).".000-0000";

    




    #get the parts that have already been printed
    open(PARTS, $partlist) || die "Can't open file: $!";
    chomp(@PARTS = <PARTS>);
      
    %hash_table = (@PARTS);
    close PARTS;

if ( defined( $hash_table{ $ordnum } ) ) {        

#??

        }
else

    {
    my $verifile = $outbox.$ordnum.".dat";

    ##Open output file to write to 
    open(OUTPUT_FILE, ">>$verifile")||die "Can't open file: $!";
    print OUTPUT_FILE ".$ordnum.",".",".$shipid.","."".$customer.",".d
+ate_z."\n";            
    close OUTPUT_FILE;

    } #end if part defined

    #add the part to the text file so we don't print it in the future
my $partfile = $partlist;
open(OUTPUT_FILE, ">>$partfile")||die "Can't open file: $!";
print OUTPUT_FILE $ordnum."\n";
close OUTPUT_FILE;
close LOG_FILE;
        

}# end of while FetchRow


    
$Db->Close();        
    


}#end for each datasource
[download]

If you want to map an array into a hash, replace

    %hash_table = (@PARTS);
[download]

with

    %hash_table = map { $_ => 1 } @PARTS;
[download]

What your code does is to use odd entries as the hash key and even entries as the value of that key. The alternative says each entry is a hash-key, having a (trivial) value of 1.

One suggestion: move the code that opens/loads <PARTS> before the loop through your database.

Good luck!

Thanks, apl! Sorry it took me so long to get back. Your suggestions above work. Here is my updated code. Hooray hooray! Gray hair turning back to brown! -Hu

#!/bin/perl

#use strict;
#allows use of the copy and move commands for files
use File::Copy;
#allows for parsing words
use Text::ParseWords;
use Win32::ODBC;
use Time::Local;
use POSIX;
use Data::Dumper;
#for emailing errors
use Net::SMTP;

# oracle uid and pw
my $user= 'test';
my $passwd = 'test';
#test

#######Prod Variables
my $outbox = "D:\\scripts\\outbox\\";
my $archive = "D:\\scripts\\archive\\";
my $log = "D:\\scripts\\log\\log.txt";
my $partlist = "D:\\scripts\\partlist.txt";

#put view in an array to loop through each db
@datasources = ("my_datasource");


       
foreach $datasource (@datasources) {
    
#open logfile
open(LOG_FILE, ">>$log")||die "Can't open file: $!";
print LOG_FILE ("begin\n");
        
#initiate DB
($Db) = new Win32::ODBC("DSN=$datasource;UID=$user;PWD=$passwd");
if (!$Db){
print LOG_FILE ("Could not open connection to DSN because of [$!]\n");
die "Could not open connection to DSN because of [$!]";
print LOG_FILE ($Db->Error()."\n");
print ($Db->Error()."\n");
}
## populate the string that will be used for the SQL query 
my $statement;
$statement = "SELECT DISTINCT a.ordnum ORDNUM, a.shipid SHIPID,  a.dat
+e DATE";
$statement = $statement." FROM new_view a";
$statement = $statement." order by ORDNUM";

$Db->Sql($statement);



    #get the parts that have already been printed
    open(PARTS, $partlist) || die "Can't open file: $!";
    chomp(@PARTS = <PARTS>);
      
    #%hash_table = (@PARTS);
    %hash_table = map { $_ => 1 } @PARTS;
    close PARTS;


##Step through each returned row
while ($Db ->FetchRow()) {
    my (%dataRow) = $Db->DataHash();
        
    my $ordnum = $dataRow{'ORDNUM'};
    my $shipid = $dataRow{'SHIPID'};
    my $date = $dataRow{'DATE'};
    #set custmoer variable for 
    if ($shipid eq '12345678') {
        $customer='CUSTOMER1';
    }
    else {
        $customer='OTHERS';
    }

    #put date in zulu format
    my $date_z = substr($dataRow{'DATE'},0,10)."T".substr($dataRow{'DA
+TE'},-8).".000-0000";

    


if ( defined( $hash_table{ $ordnum } ) ) {        

#??

        }
else

    {
    my $verifile = $outbox.$ordnum.".dat";

    ##Open output file to write to 
    open(OUTPUT_FILE, ">>$verifile")||die "Can't open file: $!";
    print OUTPUT_FILE ".$ordnum.",".",".$shipid.",".$customer.",".date
+_z."\n";            
    close OUTPUT_FILE;

    } #end if part defined

    #add the part to the text file so we don't print it in the future
my $partfile = $partlist;
open(OUTPUT_FILE, ">>$partfile")||die "Can't open file: $!";
print OUTPUT_FILE $ordnum."\n";
close OUTPUT_FILE;
close LOG_FILE;
        

}# end of while FetchRow


    
$Db->Close();        
    


}#end for each datasource
[download]