# -----------------------------------------------------------------------------
# Start processing.
# -----------------------------------------------------------------------------

# Validation.
if ($StartTime eq "")       { Error($INVALID_START_DATE);   }
if ($EndTime eq ""  )       { Error($INVALID_END_DATE);     }
unless(IsDate($StartTime))  { Error($INVALID_START_DATE);   }
unless(IsDate($EndTime))    { Error($INVALID_END_DATE);     }


$SQL = "";
$SQL = $SQL . "SELECT ";
$SQL = $SQL . "to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIME, ";
$SQL = $SQL . "to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME, ";
$SQL = $SQL . "to_char(pp.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME, ";
$SQL = $SQL . "to_char(pp.STARTTIME, 'YYYY-MM-DD HH:MM:SS') STARTTIME, ";
$SQL = $SQL . "pp.SESSIONID, ";
$SQL = $SQL . "pp.PLANID ";
$SQL = $SQL . "from ";
$SQL = $SQL . "\\\$hist_plan_prolog_1 pp ";
$SQL = $SQL . "where ";
$SQL = $SQL . "pp.SUBMITTIME between '2014-06-26 05:00' and '2014-06-26 08:00:00' ";
$SQL = $SQL . "order by ";
$SQL = $SQL . "pp.SUBMITTIME";

print "[SQL]:\n\n\n$SQL\n\n";

@Output = qx { /nz/kit/bin/nzsql -d histdb  -c "$SQL" };
$RetCode=$?;
chomp(@Output);

foreach ( @Output ) { print "$_\n"; }

##</code><code>##

SELECT to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIME, to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME, to_char(pp.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME, to_char(pp.STARTTIME, 'YYYY-MM-DD HH:MM:SS') STARTTIME, pp.SESSIONID, pp.PLANID from \$hist_plan_prolog_1 pp where pp.SUBMITTIME between '2014-06-26 05:00' and '2014-06-26 08:00:00' order by pp.SUBMITTIME

ERROR:  'SELECT to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIME, to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME, to_char(pp.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME, to_char(pp.STARTTIME, 'YYYY-MM-DD HH:MM:SS') STARTTIME, pp.SESSIONID, pp.PLANID from $hist_plan_prolog_1 pp where pp.SUBMITTIME between '2014-06-26 05:00' and '2014-06-26 08:00:00' order by pp.SUBMITTIME'
error                                                                                                                                                                                                                                                                        ^ found "$" (at char 261) expecting an identifier found a keyword

##</code><code>##

my $SQL1=<<"_SQL1_";
SELECT
    to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIME,
    to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME,
    to_char(pp.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME,
    to_char(pp.STARTTIME, 'YYYY-MM-DD HH:MM:SS') STARTTIME,
    to_char(pe.ENDTIME, 'YYYY-MM-DD HH:MM:SS') ENDTIME,
    (DATE_PART('second', pe.ENDTIME - pp.STARTTIME )) Seconds_Execution,
    pp.SESSIONID,
    pp.PLANID,
    sp.SESSIONUSERNAME,
    pp.ESTIMATEDCOST,
    pp.ESTIMATEDDISK,
    pp.ESTIMATEDMEM,
    pp.TOTALSNIPPETS,
    pe.RESULTROWS,
    pp.NPSID,
    pp.NPSINSTANCEID,
    pp.OPID,
    pp.LOGENTRYID
from
    '\$hist_nps_1' ns,
    '\$hist_session_prolog_1' sp,
    '\$hist_plan_prolog_1' pp,
    '\$hist_plan_epilog_1' pe
where
    ns.NPSID = pp.NPSID
and ns.NPSID = pe.NPSID
and ns.NPSID = sp.NPSID
and sp.SESSIONID = pp.SESSIONID
and sp.SESSIONID = pe.SESSIONID
and pp.OPID = pe.OPID
and pp.SUBMITTIME between '$StartTime' and '$EndTime'
order by
    pp.SUBMITTIME
_SQL1_

@Output = qx { /nz/kit/bin/nzsql -d histdb  -c "$SQL1" };
$RetCode=$?;
chomp(@Output);

##</code><code>##

ERROR:  'SELECT
        to_char(pp.SUBMITTIME, 'YYYY-MM-DD HH:MM:SS') SUBMITTIME,
        to_char(pp.QUEUETIME, 'YYYY-MM-DD HH:MM:SS') QUEUETIME,
        to_char(pp.PREPTIME, 'YYYY-MM-DD HH:MM:SS') PREPTIME,
        to_char(pp.STARTTIME, 'YYYY-MM-DD HH:MM:SS') STARTTIME,
        to_char(pe.ENDTIME, 'YYYY-MM-DD HH:MM:SS') ENDTIME,
        (DATE_PART('second', pe.ENDTIME - pp.STARTTIME )) Seconds_Execution,
        pp.SESSIONID,
        pp.PLANID,
        sp.SESSIONUSERNAME,
        pp.ESTIMATEDCOST,
        pp.ESTIMATEDDISK,
        pp.ESTIMATEDMEM,
        pp.TOTALSNIPPETS,
        pe.RESULTROWS,
        pp.NPSID,
        pp.NPSINSTANCEID,
        pp.OPID,
        pp.LOGENTRYID
from
        '' ns,
        '' sp,
        '' pp,
        '' pe
where
        ns.NPSID = pp.NPSID
and     ns.NPSID = pe.NPSID
and ns.NPSID = sp.NPSID
and sp.SESSIONID = pp.SESSIONID
and sp.SESSIONID = pe.SESSIONID
and pp.OPID = pe.OPID
and pp.SUBMITTIME between '2014-06-26 05:00:00' and '2014-06-26 08:00:00'
order by
        pp.SUBMITTIME
'
error                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       ^ found "'" (at char 580) expecting an identifier found a keyword