Here's an example of a simple calculation with a User Defined Function

#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh=DBI->connect("dbi:CSV(RaiseError=1):csv_eol=\n");

sub MyCalc { $_[3] * 2 }
$dbh->do("CREATE FUNCTION MyCalc");

my $sth = $dbh->prepare(q{  SELECT sales,MyCalc(sales) FROM Test });
$sth->execute;
$sth->dump_results();
__END__
#
# given sales values of 2 and 5, this script outputs
#
#    2,4
#    5,10
[download]

See SQL::Statement::Functions for more on creating User Defined Functions.

CREATE TABLE new AS SELECT $cols FROM old1 JOIN old2 WHERE ...

Interesting idea of using a function within the SQL statement to combine the sets to a third table on-the fly. I have no experience in this, but it seems very doable. I will have to do the required reading before starting. Thank you for the cool option.

If so, a solution is straightforward. You'll find elements in the first hash but not the second as you iterate over it. If you delete elements from the second hash as you find them, anything left in the second hash will be items that weren't in the first hash.

If there can be a many-to-one correspondence between the two, you can pull the first array into a hash with selectall_hashref, find elements in the first but not the second while iterating over the first array, then iterate over the second array to search for items which aren't in the first.

You can also have the SQL server do most of the work for you by JOINing the tables in an appropriate way. For example, a SELECT ... FROM table1 LEFT JOIN table2 ... will return all elements of table1, where possible joining the data from table2, and where not replacing it with null. SELECT ... FROM table2 LEFT JOIN table1 WHERE ... AND table1.something IS NULL will return all items in table2 with no corresponding row in table1. Maybe a better SQL hacker than I would know one query to efficiently do the whole thing.

No, the data sets are from different markets, with different values. What I want to do is create a third data set by performing calculations on the values of each set and putting the result in the new data set. If a row exists in one but not the other, I would simply take the previous row of the non-existing row's data set and perform the calculations using those values. So, joining the values via SQL statements is not what I am looking for. Thanks for your input though.

Ah, I guess I don't understand your problem then. So you're saying if you have a missing day in one of your data sets, you want to reuse the previous day's data for calculations on that row? If so, sort the two data sets by date, then move through both in parallel. Track the "current row" and "next row" for both data sets, and loop over a series of dates. For each data set, if the date of "next row" is equal to the date you're currently looking at, set "current row" to "next row" and read another item into "next row". Then perform calculations based on the each data set's "current row", which will either contain the data from the current day or from the last day for which data is available.

How to deal with a missing first row would have to be a special case.

Hope this helps; no time to write up any sample code right now.

#!/usr/bin/perl -w
use strict;
use DBI;

my $pair1 = shift;
my $pair2 = shift;

my $dbh = DBI->connect("DBI:CSV:")
    or die "Cannot connect: " . $DBI::errstr;
$dbh->{RaiseError} = 1;
my $query1 = "SELECT * FROM $pair1";
my $query2 = "SELECT * FROM $pair2";

my $getpair1 = $dbh->prepare($query1);
my $getpair2 = $dbh->prepare($query2);

my %pair1_hash = do{
    $getpair1->execute();
    while($_ = $getpair1->fetchrow_hashref()){
        $_{$_->{Date}} = $_;
    }
    %_;
};
my %pair2_hash = do{
    $getpair2->execute();
    while($_ = $getpair2->fetchrow_hashref()){
        $_{$_->{Date}} = $_;
    }
    %_;
};
my %combo_hash = do{
    map{
        if(!defined $pair1_hash{$_}){
            $_, $pair2_hash{$_};
        }elsif(!defined $pair2_hash{$_}){
            $_, $pair1_hash{$_};
        }else{
            #computations here to combine the contents of 
            #%pair1_hash and %pair2_hash. Utilize the fact that 
            #the last statement evaluated will be the return value.

            #just as and example

            my %t;
        
            #reference to specific fields within each hash referred to
            #by $pair1_hash{$_} and $pair2_hash respectively
        
            $t{Open} = sprintf("%.2f",($pair1_hash{$_}->{Open} + $pair
+2_hash{$_}->{Open} / 2));
            $t{High} = sprintf("%.2f",($pair1_hash{$_}->{High} + $pair
+2_hash{$_}->{High} / 2));
        
            #So finally we return the key and a reference to 
            #our computed hash.

            $_,\%t;
        }
    }do{
        undef @_{keys %pair1_hash, keys %pair2_hash};
        sort keys %_;
        };
};
[download]

THIS looks like what I had in mind. Because I am more familiar with this code, I will work with this before trying the SQL table join above. Thank you for all the replies, I will try this out now and let you know how it turns out.

Peace be unto you.