Siva Siva - 4 months ago 18
SQL Question

I need to write a oracle sql query as from date < to date and i should exit if it's a future date in Perl

I have a perl script to fetch the data from Db in a particular period for that i need to write a script to validate the, user asking period dates are valid or not

Start and end dates are tacking from user like 01-JUL-16 12-JUL-16, below is my
code that i wrote, unfortunately it is not working, not sure why

my $dt=`date +%d-%m-%Y`;
chop $dt;
my @res1 = `sqlplus -S $logon <<EOF
set echo off
set heading off
select case when '$start_dt' > '$end_dt' then 1 else 0 ||','|| when `$end_dt' > '$dt' then 2 else 3 end from dual;
quit;
EOF`;

foreach( @res1 )
{
chop;
next if /^$/;
next if !/HCL/;
my @fields = undef;
@fields = split(/\s+/,$_);
if ( $fields[0] eq "1")
{
die"start dat is greater than end date\n";
}
else
{
print"st dt less than end_dt";
}
if( $fields[1] eq "2")
{
print"you gave future date, u wnat proced [Y/N] \n"
$a=<>;
if($a eq "Y" || $a eq "y")
{
print"u gave yes\n";
}
else
{
die "exiting due to futur date\n"
}
}
}





Update



Perl reformatted to make it readable

my $dt = `date +%d-%m-%Y`;
chop $dt;

my @res1 = `sqlplus -S $logon <<EOF
set echo off
set heading off
select case when '$start_dt' > '$end_dt' then 1 else 0 ||','|| when `$end_dt' > '$dt' then 2 else 3 end from dual;
quit;
EOF`;

foreach (@res1) {

chop;

next if /^$/;
next if !/HCL/;

my @fields = undef;
@fields = split( /\s+/, $_ );

if ( $fields[0] eq "1" ) {
die "start dat is greater than end date\n";
}
else {
print "st dt less than end_dt";
}

if ( $fields[1] eq "2" ) {

print "you gave future date, u wnat proced [Y/N] \n"
$a = <>;

if ( $a eq "Y" || $a eq "y" ) {
print "u gave yes\n";
}
else {
die "exiting due to futur date\n";
}
}
}

Answer

As far as I understand your question you want to compare $start_dt, $end_dt, and $dt.

It would make things easier if you'd told us exactly what the actual values of these variables are (and ideally how they are obtained). For the future, please state their exact format. "Like 01-JUL-16" is not something a Perl program can interpret without further details. The expression "1-July-2016" is also like "01-JUL-16" -- somehow, at least.

Having said that, I suggest the following code. It firstly converts the user input into values that are easier to compare and then does so with simple string comparison operators.

#!/usr/bin/env perl

use strict;
use warnings;
use POSIX qw(strftime);

# data from user input
my $start_dt = '01-JUL-16';
my $end_dt   = '12-JUL-16';

# convert user input to upper case ("uc") in case the input is mixed
# and/or lower case. for lower case use "lc" and adjust the line
# with "%MONTH_NUMBERS = ..." accordingly.
$start_dt = uc($start_dt);
$end_dt   = uc($end_dt);

# create a hash with "JAN=>1, FEB=>2, MAR=>3, ..." in your current locale
my %MONTH_NUMBERS = map { uc( POSIX::strftime( "%b", 0, 0, 0, 1, $_ - 1, 95 ) ) => $_ } ( 1 .. 12 );

# returns "now" in the format 'YYYY-MM-DD' (e.g. for today '2016-07-16')
my $dt = POSIX::strftime( '%Y-%m-%d', localtime() );

# convert $start_dt and $end_dt into 'YYYY-MM-DD'.
# This assumes your input years are in the range 00-99 and actually mean 2000-2099.
# If that's wrong, then you need to adjust the "2000+$3" part.
$start_dt =~ s/(\d\d)-(\D\D\D)-(\d\d)/ sprintf('%d-%02d-%02d', 2000+$3, $MONTH_NUMBERS{$2}, $1) /e;
$end_dt =~ s/(\d\d)-(\D\D\D)-(\d\d)/ sprintf('%d-%02d-%02d', 2000+$3, $MONTH_NUMBERS{$2}, $1) /e;

# now the values are:
#   dt       : 2016-07-16
#   start_dt : 2016-07-01
#   end_dt   : 2016-07-12

# you can compare them now with simple string operations, i.e. 'le', 'eq', 'gt' and so forth:
if ($end_dt gt $dt) {
    print "end date is in the future\n";
} elsif ($start_dt le $end_dt) {
    print "start date is less than or equal to end date\n";
} else {
    print "start date is greater than end date\n";
}