How to add a column alias inside a array reference (do_dataset)

I get an error on my script on line 47, which outputs few database columns to a file.  Attempting to alias to_char(starttime...) as studdate (student date) but it fails with an error - invalid identifier. How do I resolve the error?  

I am trying to have file output column as Studdate and not "TO_CHAR(STARTTIME 'MM/DD/YYYY')".

Line 47
do_dataset($timeparm,'_STUDLOC_', [qw(STUDNAME STUDCODE TO_CHAR(STARTTIME 'MM/DD/YYYY')  "STUDDATE" TOTALS)], status => "'N'");

Open in new window




Here's the complete script ...
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;


#Check to see if required parameters are received by appworx
unless($db && $user && $pw && $wkdir && $firstday && $lastday && $timeparm)
{
  die("\n\n  - ERROR:  The Required number of arguments were not received.  Verify that the AppWorx prompts are correct and reset the module..\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());

# Create the directory passed in by appworx in event the directory does not exist, do nothing if the directory already exists
mkpath("$wkdir");
 
my $partition_key = join '', $firstday ,'_',$lastday;
print("Partition Key : $partition_key \n\n");


my $sthcounts = $dbh->prepare(qq(select count(distinct r.execution_id) from studen_trans r where r.partition_key = ? AND r.status = 'N' ));

# Values of partition key will be passed on by appworx will be parameterized in the script
$sthcounts -> execute($partition_key);


my $rowcount = ($sthcounts-> fetchrow_array());
$sthcounts -> finish();


die "ERROR: NO DATA EXISTS FOR GIVEN FIRSTDAY LASTDAY SET OF DATES - ABORTING!!!\n\n"
    unless $rowcount;

# Datasets for which files will  be processed.  The script will output a total of 5 files.
# Creating each dataset as an array reference. The array is passed as list of columns into the $my allrows statement later in the script

do_dataset($timeparm,'_STUDID_', [qw(STUDID STUDADDR  TOTALS)], status => "'N'");
do_dataset($timeparm,'_STUDLOC_', [qw(STUDNAME STUDCODE TO_CHAR(STARTTIME 'MM/DD/YYYY') "STUDDATE" TOTALS)], status => "'N'");

# Subroutine does processing of datasets
# file is filename passed in from the do_dataset sub
# col is the list of columns
# opt is the list of options such as Where/Group by clauses

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)) : '';

    #Initialize parameters filedate and getfilename to be used for filename output
    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

    # 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();

Open in new window

steve2312Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ozoCommented:
# did you mean this?
do_dataset($timeparm,'_STUDLOC_', ['STUDNAME', 'STUDCODE', "TO_CHAR(STARTTIME 'MM/DD/YYYY')", "STUDDATE","TOTALS"], status => "'N'");
steve2312Author Commented:
Correct. Studdate will be alias to the to_char (starttime .....) column. The output would display studdate along  with other columns.
steve2312Author Commented:
Will test it out and post results..
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

steve2312Author Commented:
This may not work as the to_char is still getting displayed in the output.

The to_char(.....) should go away with only  the "alias column"  studdate be displayed for that field.. Studdate is not a database column, but an alias reference to the to_char(.....) function.

do_dataset($timeparm,'_STUDLOC_', ['STUDNAME', 'STUDCODE', "TO_CHAR(STARTTIME 'MM/DD/YYYY')", "STUDDATE","TOTALS"], status => "'N'");

Open in new window


Present output header

STUDNAME,STUDCODE,TO_CHAR(STARTTIME,'MM/DD/YYYY') STUDDATE,TOTALS

Open in new window



Expected output header:

STUDNAME,STUDCODE, STUDDATE,TOTALS

Open in new window

steve2312Author Commented:
do_dataset($timeparm,'_STUDLOC_', ['STUDNAME', 'STUDCODE', "to_char(STARTTIME,'MM/DD/YYY')","TOTALS"]

Open in new window


The alias just does not work inside the subroutine.   Is there a way to look for character string "Starttime" and replace it with tradedate (something along lines of regexp perhaps) while writing out the columns to the file in code snippet (lines 77 to 86 from original code paste???

# 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

Open in new window

steve2312Author Commented:
I resolved the issue by using a search and replace string prior to displaying the output in the script

$col =~ s/to_char(STARTTIME,'MM/DD/YYY')/STUDDATE /ig;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
steve2312Author Commented:
Used a search and replace string syntax as indicated in the last comment
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.