Fuism has asked for the wisdom of the Perl Monks concerning the following question:

I have a question about my script. I cant seem to figure out why it keeps appending data from the previous query to the new query. So for instance the 1st query returns (1,2,3) then second query runs and returns (1,2,3(this is not suppose to be in the new file) and then 2,3,4 this is what its only suppose to return). Thanks in advance, below is the code. 
print "Setting Database Connection to CDW-P02.....\n";
# setting up database connection to CDW-V                   
my $dbv = DBI->connect( 'dbi:Oracle:Test' , "$user1" , "$passw") 
    || die "Database connection not made: $DBI::errstr";

# Query To find APR Decline
$apr_decl = "SELECT CRAC_EVNT_DATA_T, COUNT(*)\n".
"FROM CAMMGR.CRAC_EVNT_FCT a, CAMMGR.CRAC_DIM B\n".
"WHERE A.CRAC_DIM_I =B.CRAC_DIM_I\n".
"AND CRAC_EVNT_SCRN_I = 'RDGR  '\n".
"AND CRAC_EVNT_D >= TO_DATE('$first_date','YYYYMMDD')\n".
"AND CRAC_EVNT_D <= TO_DATE('$last_date','YYYYMMDD')\n".
"GROUP BY CRAC_EVNT_DATA_T\n";


#Opens files to write to    
die("Cannot open SAS file to write to.")
    unless(open(APR, ">APR_Decline_$today.csv"));

# Runs Query
@ap = &run_query($apr_decl);
print APR "CRAC_EVNT_DATA_T, Number Declined\n";
print APR "@ap";

close(APR);



# Query captures credit line determinator declines by reason
$decl_reason = "SELECT CRAC_EVNT_DATA_T, COUNT(*)\n".
"FROM CAMMGR.CRAC_EVNT_TYPE A, CAMMGR.CRAC_EVNT_FCT B, CAMMGR.CRAC_DIM
+ C\n".
"WHERE A.CRAC_EVNT_TYPE_C =B.CRAC_EVNT_TYPE_C\n".
"AND B.CRAC_DIM_I = C.CRAC_DIM_I\n".
"AND A.CRAC_EVNT_TYPE_C = 500\n".
"AND CRAC_EVNT_DATA_T LIKE '  +00000000000.00CL00%'\n".
"AND CRAC_EVNT_D >= TO_DATE('$first_date','YYYYMMDD')\n".
"AND CRAC_EVNT_D <= TO_DATE('$last_date','YYYYMMDD')\n".
"GROUP BY CRAC_EVNT_DATA_T\n";

#Opens files to write to    
die("Cannot open SAS file to write to.")
    unless(open(REASON, ">Decline_Reason_$today.csv"));

# Runs Query
@reason = &run_query($apr_decl);
print REASON "CRAC_EVNT_DATA_T, Number Declined\n";
print REASON "@reason";

close(REASON);

sub run_query {    
    my ($query) = @_;
    # Run Query
    print "$query\n";
    ### Prepare a SQL statement for execution
    $prepare_exe = $dbv->prepare( "$query");
  
    ### Execute the statement in the database
    $prepare_exe->execute;    
    
    # Each element will be comma seperated
    while ( @row = $prepare_exe->fetchrow_array(  ) ) {
            $string = join(",", @row);
            $string = $string."\n";
            #print "$string\n";
            # Will contain all rows
            push(@new_row, $string);
    }
    return @new_row;
    $prepare_exe->finish;
}

[download]

Replies are listed 'Best First'.
Re: Query Subroutine
by davidrw (Prior) on Aug 08, 2005 at 02:35 UTC
    Should the second run_query call be for $decl_reason ? What i see is (looks like running the same query both times): 
    $apr_decl = "SELECT ...";
# Runs Query
@ap = &run_query($apr_decl);
print APR "CRAC_EVNT_DATA_T, Number Declined\n";
print APR "@ap";

$decl_reason = "SELECT ...;"
# Runs Query
@reason = &run_query($apr_decl);
print REASON "CRAC_EVNT_DATA_T, Number Declined\n";
print REASON "@reason";

    [download]
    If that's not the issue, can you provide output from the debugging statements you have in there?

    general question (i see this is just a snippet) -- do you have use strict; and use warnings; off or are you just pre-declaring all the vars at the top?
[reply]
[d/l]
[select]
      Pre-declaring at the top. I have been told to always use strict and warnings but I dont... I basically just copy and paste the code just changing the query statement. I didnt even see what you pointed out that I forgot to change the variable.
[reply]
        I have been told to always use strict and warnings but I dont.
        Definitely do. You're just asking for nasty debugging problems if you don't .. in general, no reason not to--actually the opposite because it will save time by pointing out problems and potential problems to you. Try adding it to this program and see what pops..
        Pre-declaring at the top.
        Take a look at the thread coding rules, and in particular merlyn's reply Re: coding rules
[reply]
Re: Query Subroutine
by chromatic (Archbishop) on Aug 08, 2005 at 03:41 UTC

    You have not scoped @new_row to run_query(), so every time you call the function, you push values onto the same array. The solution to this is to write:

    my @new_row;

    ... just before the while loop. I encourage you to scope all of your variables lexically in the tightest possible scope; it will help you avoid many problems like this.

[reply]
[d/l]
[select]
      So do you mean like this? 
      sub run_query {    
    my ($query) = @_;
    # Run Query
    print "$query\n";
    ### Prepare a SQL statement for execution
    $prepare_exe = $dbv->prepare( "$query");
  
    ### Execute the statement in the database
    $prepare_exe->execute;    
    my @new_row;
    # Each element will be comma seperated
    while ( @row = $prepare_exe->fetchrow_array(  ) ) {
            $string = join(",", @row);
            $string = $string."\n";
            #print "$string\n";
            # Will contain all rows
            push(@new_row, $string);
    }
    return @new_row;
    $prepare_exe->finish;
}

      [download]
      Thanks...
[reply]
[d/l]

        Right. You ought to do that for the other lexical variables too, to avoid similar problems (though with them being all scalars and you not calling any other code likely to change them, you're safe for now). I would write that subroutine more like:

        sub run_query
{
    my ($query) = @_;

    my $sth = $dbv->prepare( $query );
    $sth->execute();

    my @new_row;

    while ( my @row = $sth->fetchrow_array() )
    {
        my $string  = join( ',', @row );
        $string    .= "\n";

        push( @new_row, $string );
    }

    return @new_row;
}

        [download]

        I removed the code after the return call because Perl will never execute it. (Further, if you exhaust the result set, as this code does, there's no need to call finish().)

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom