CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
How to alias a to_char function with a pseudocolumn in output header

 



steve798
Novice

Apr 12, 2018, 8:47 AM

Post #1 of 2 (3801 views)
How to alias a to_char function with a pseudocolumn in output header Can't Post

The perl script below reads columns from a oracle db and writes to a file. I need to display the output header replacing the to_char(studrolltime,'mm/dd/yyyy') with a pseudo column "Student_date"


Code
 
use strict;
use warnings;
use DBI;
use Date::Manip;
use File::Path;

Date_Init('TZ=US/Central','Internal=1');

$| = 1;

my ($db, $user, $pw, $wkdir, $firstday, $lastday, $timeparm) = @ARGV;


unless($db && $user && $pw && $wkdir && $firstday && $lastday && $timeparm)
{
die("\n\n - ERROR: The Required number of arguments were not received...\n\n");
}

# Connectivity check to the database else display failed connectivity
my $dbh = DBI->connect("dbi:Oracle:$db", $user, $pw, { AutoCommit => 0, RaiseError => 1 }) or die("Cannot connect to Database '$db' " . DBI->errstr());


# Capture counts for distinct partitions for given firstday and lastday dates
my $partition_key = join '', $firstday ,'_',$lastday;
print("Partition Key : $partition_key \n\n");

do_dataset($timeparm,'_STUDIID_', ["STUDID","STUDCODE","TO_CHAR(STUDROLLTIME,'MM/DD/YYYY')", "STUDNAME","STUDTOTALS"]);
do_dataset($timeparm,'_STUDREP_', ["STUDNAME","STUDCODE","TO_CHAR(STUDROLLTIME,'MM/DD/YYYY')"]);


sub do_dataset {
my ($timeparm, $file, $cols, %opt) = @_;
my $col = join ',', @$cols;
my $group = ' ';

# process the below if the above dataset should have a GROUP BY clause
if (exists $opt{group_by}){
$group = join ' ', @{$opt{group_by}};
delete $opt{group_by};
}


my $extra = %opt ? ('and ' . join(' and ', map { "$_ = $opt{$_}" } keys %opt)) : '';


my $filedate;

if (lc $timeparm eq 'yearly') {
$filedate = substr($firstday,0,4); # Get four digit year to append to the filename
} else {
$filedate = join '',substr($firstday,4,2), substr($firstday,0,4); # Append 2 digit month with 4 digit year to filename
}

my $getfilename = $timeparm.$file.$filedate.'.csv'; #append period of data sought

# Select the complete dataset from the table and ready it for print out to the work directory
my $allrows = $dbh->selectall_arrayref("SELECT DISTINCT $col FROM STUDENTREC WHERE PARTITION_KEY = '$partition_key' $extra $group")
or die("Could not get data from table ".DBI->errstr);


# Write out files generated to the directory.
my $dumpdir = "$wkdir/$getfilename";

open OUT, '>', $dumpdir or die " - Could not open $dumpdir for export. $!";
print OUT $col, "\n"; # manually list column names from input
print OUT map {join(',', map { defined($_) ? $_ : '' } @$_), "\n" } @$allrows; # print out all the columns
close OUT;

# File output process completion
print "The $dumpdir file is generated to output directory.\n\n";
}


# Disconnect from the database
$dbh->disconnect();



present output header


Code
STUDNAME  STUDCODE TO_CHAR(STUDROLLTIME,'MM/DD/YYYY')


expected output header (where student date will be alias for to_char(..)

Code
STUDNAME  STUDCODE STUDENT_DATE



steve798
Novice

Apr 12, 2018, 12:08 PM

Post #2 of 2 (3794 views)
Re: [steve798] How to alias a to_char function with a pseudocolumn in output header [In reply to] Can't Post

I was able to resolve this by using a search and replace pattern like..in the script



Code
$col =~ s/TO_CHAR(STUDROLLTIME,'MM/DD/YYYY')/STUDENT_DATE /ig;


 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives