Complete

sub load_data { $dbh=DBI->connect("dbi:Oracle:$db","$luser","$pass",{ PrintError=>1,AutoCommit => 0 }) or &error("cannot connect: $DBI::errstr\n"); #open(INFILE,">$dir")||&error("Cant open $dir: $!"); open (FILE,$dir)||&error("Can't open $dir: $!"); while ($line=) { chomp($line); ($id, $value)=split/\|/, $line; push (@edits,$line); } close FILE; $x=@edits; $i=0; while ($i<$x) { ($id,$value)=split/\|/, $edits[$i]; $q=qq{insert into convert.sql_edits values (\'$id\',\'$value\')}; $sth=$dbh->prepare($q); $sth->execute; push(@total,$sth->rows); $i++; } $xtotal=@total; $sth->finish; if ($type eq "other") { $table="convert.b"; } elsif ($type eq "input"){ $table="convert.i"; } elsif ($type eq "output"){ $table="convert.o"; } elsif ($type eq "standard"){ $table="convert."; } if ($field eq "p1h_secondary_dx") { $sql="update $table"."dx$proc a set $field=(select trim(edit_value) from convert.sql_edits b where a.counter=b.id) where exists (select 1 from convert.sql_edits b where a.counter=b.id)"; } elsif ($field eq "p1h_admit_date"||$field eq "p1h_admit_dx"||$field eq "p1h_admit_hour"||$field eq "p1h_admit_phys"|| $field eq "p1h_attending_phys"||$field eq "p1h_birth_date"||$field eq "p1h_consulting_phys1" ||$field eq "p1h_consulting_phys2"||$field eq "p1h_consulting_phys3"||$field eq "p1h_drg_hosp" ||$field eq "p1h_hosp_admit_source"||$field eq "p1h_hosp_admit_type"||$field eq "p1h_hosp_admit_type" ||$field eq "p1h_hosp_disch_disposition"||$field eq "p1h_hosp_pat_type"||$field eq "p1h_hosp_patient_type" ||$field eq "p1h_hosp_payor_code"||$field eq "p1h_hosp_reimb_serv_code"||$field eq "p1h_hosp_service_code" ||$field eq "p1h_mdc_hosp"||$field eq "p1h_prev_disch_date"||$field eq "p1h_prin_dx"||$field eq "p1h_race" ||$field eq "p1h_readmit_flag"||$field eq "P1H_REFERRING_PHYS"||$field eq "p1h_reimb_amt"||$field eq "p1h_sex_code" ||$field eq "p1h_site_of_reimb_calc"||$field eq "p1h_tissue_result") { $sql="update $table"."hdr$proc a set $field=(select trim(edit_value) from convert.sql_edits b where a.header_counter=b.id) where exists (select 1 from convert.sql_edits b where a.header_counter=b.id)"; } elsif ($field eq "p1h_procedure_code"||$field eq "p1h_px_date"||$field eq "p1h_surgeon"){ $sql="update $table"."epi$proc a set $field=(select trim(edit_value) from convert.sql_edits b where a.counter=b.id) where exists (select 1 from convert.sql_edits b where a.counter=b.id)"; } elsif ($field eq "p2h_chg_amt"||$field eq "p2h_cpt_code"||$field eq "p2h_ordering_phys_code"||$field eq "p2h_serv_unit_count" ||$field eq "p2h_service_date") { $sql="update $table"."det$proc a set $field=(select trim(edit_value) from convert.sql_edits b where a.trailer_counter=b.id) where exists (select 1 from convert.sql_edits b where a.trailer_counter=b.id)"; } $sth=$dbh->do("$sql")||&error("Unable to perform update statement:

$sql

$DBI::errstr\n"); $updated=$sth->rows; $dbh->commit() || &error("Unable to commit changes made by the update statement:

$sql

$DBI::errstr\n");; $sth->finish; print $co->header(); print "Complete

Summary

Upload

The file $the_file was successfully uploaded.

The destination file $dir was created.

File

$xtotal records were loaded into the convert.sql_edits table

Updates

The update statement run was:

$sql

A Total of $updated records were updated

"; system ("rm $dir"); $dbh->disconnect or warn "disconnect failed: $DBI::errstr\n"; print "You have been disconnected from the database. All data in convert.sql_edits for your current session has been automatically deleted.

"; exit; } #end load_data