Hi list,
The following code works find without decode but it is crapping out with
decode function, is there a way to make it work?
Code follows;
#!/usr/bin/perl
use warnings;
use FileHandle;
use DBI;
use strict;
use DBI qw(:sql_types);
use Spreadsheet::WriteExcel;
#use Spreadsheet::WriteExcel::Big;
use Getopt::Long;
use constant LINES_PER_BOOK => 60001;
my $dbh1 = DBI->connect("dbi:Oracle:test.com", "test", "test123" ) or die
"Can't make 1st database connect: $DBI::errstr\n";
$dbh1->{RowCacheSize} = 100;
my $sth = $dbh1->prepare( "
select
app.UNDERWRITER_PORTAL_LOGIN \"Underwriter Login",
u.last_name \"Last Name",
u.first_name \"First Name",
c.company_name \"Company Name",
decode(alog.ROW_MOD_BY,null,'system',alog.row_mod_by) row_mod_by \"Status Mod
By",
decode( alog.ROW_MOD_DATE,null,alog.row_create_date,alog.row_mod_date)
row_mod_date \"Modifed Date",
codes.STATUS_CODE \"Status Code",
app.SUBMIT_id \"Submit ID"
from
tg_online_app app ,
tg_online_app_audit_log alog ,
tg_online_app_codes codes ,
tg_company c ,
dps_user u
where
app.SUBMIT_ID = alog.SUBMIT_ID and
alog.STATUS_ID = codes.STATUS_ID and
c.id = app.COMPANY_ID and
app.submit_id!='9600015' and
app.STATUS_ID = alog.STATUS_ID and
u.login (+) = app.UNDERWRITER_PORTAL_LOGIN
order by c.COMPANY_NAME , alog.AUDIT_ID
" );
$sth->execute();
print "Creating Workbook\n";
my $workbook = Spreadsheet::WriteExcel->new(newWorkBookName());
die "unable to create workbook - $!\n" unless $workbook;
$workbook->set_tempdir('c:/pc/lc');
my $worksheet = $workbook->addworksheet();
my $colNames = $sth->{NAME_uc};
print "Fetching data\n";
my $rowCount=0;
my $lineCount=0;
$worksheet->write_row($lineCount,0,$colNames);
print "\n";
while( my $ary = $sth->fetchrow_arrayref ) {
print "." unless $rowCount++%1000;
if ( ++$lineCount >= LINES_PER_BOOK ) {
$workbook->close;
my $workBookName = newWorkBookName();
$workbook =
Spreadsheet::WriteExcel->new($workBookName);
die "unable to create workbook - $!\n" unless $workbook;
$worksheet = $workbook->addworksheet();
$lineCount=0;
$worksheet->write_row($lineCount,0,$colNames);
$lineCount=1;
print "\nNew Workbook: $workBookName\n";
}
$worksheet->write_row($lineCount,0,$ary);
}
print "\n";
$workbook->close;
$sth->finish;
$dbh1->disconnect;
{
my $workBookNumber = 0;
sub newWorkBookName {
return "Inactive_Models_" . ++$workBookNumber . ".xls";
}
}
Thanks in advance
---------------------------------
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays,
whatever.
