in reply to Merging Results from 3 different queries

If you formatted your post, you'd probably get more responses.

Assuming that each of your queries produces a hashref containing the same keys, then printing the values as you've asked might look something like this: 

my $hash_ref1 = $dbh->selectall_hashref( $sql1, $key_field1, {}, @valu
+es1 ); 
my $hash_ref2 = $dbh->selectall_hashref( $sql2, $key_field2, {}, @valu
+es2 ); 
my $hash_ref3 = $dbh->selectall_hashref( $sql3, $key_field3, {}, @valu
+es3 ); 

## for each id    
for my $id ( keys %{ $hashref1 } ) {
    ## extract an array of results for that key from each hash
    my @results1 = @{ $hashref1->{ $id } };
    my @results2 = @{ $hashref2->{ $id } };
    my @results3 = @{ $hashref3->{ $id } };

    ## join them together with spaces 
    ## and print them along with the key
    printf "%14s :  %s \t%s\t%s\n", $id, 
        join( ' ', @results1 ),
        join( ' ', @results2 ),
        join( ' ', @results3 );
}

[download]
Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
"Science is about questioning the status quo. Questioning authority".
In the absence of evidence, opinion is indistinguishable from prejudice.
"Too many [] have been sedated by an oppressive environment of political correctness and risk aversion."

Replies are listed 'Best First'.
Re^2: Merging Results from 3 different queries
by MegaVoice (Novice) on Jul 24, 2007 at 17:37 UTC
    I took your code and pulled into my script. However, I was getting an error:

    Not an ARRAY reference

    I modified the code as follows:
    for my $id ( sort keys %{ $hashref1 } ) {
    ## extract an array of results for that key from each hash
    my %results1 = %{ $hashref1->{ $id } };
    my %results2 = %{ $hashref2->{ $id } };
    my %results3 = %{ $hashref3->{ $id } };

    ## join them together with spaces 
    ## and print them along with the key
    printf "%14s :  %s \t%s\t%s\n", $id, 
        join( ' ', %results1 ),
        join( ' ', %results2 ),
        join( ' ', %results3 );
}

    [download]

    I was then able to get the data back out of the hash but it did not maintain the data order (columns) that I get when running the straight SQL. Perhaps using the selectall_hashref may not be the answer.

    If people have other suggestions for options, feel free to pass them along.
[reply]
[d/l]
[reply]
        Here is the output:
        07/20/2007 : Cardiology 371.000000 Neurology 463.000000 All_sum 2134.000000 call_date 07/20/2007 Gastroenterology 383.000000 General 800 # 455.000000 Radiology 462.000000 Cardiology 1 Neurology 6 All_sum 63 call_date 07/20/2007 Gastroenterology 4 General 800 # 19 Radiology 33 Cardiology 1 Neurology 6 All_sum 66 call_date 07/20/2007 Gastroenterology 4 General 800 # 19 Radiology 36

        07/23/2007 : Cardiology 230.000000 Neurology 501.000000 All_sum 1942.000000 call_date 07/23/2007 Gastroenterology 391.000000 General 800 # 300.000000 Radiology 520.000000 Cardiology 2 Neurology 10 All_sum 107 call_date 07/23/20 07 Gastroenterology 4 General 800 # 25 Radiology 66 Cardiology 2 Neurology 10 All_sum 110 call_date 07/23/2007 Gastroenterology 5 General 800 # 26 Radiology 67

        The columns are in the same location. I just might be a bit limited by the results of the stored procedure.

        I am looking to create something along the lines of:
        Date Data Col A Col B Col C Col D Total
        7/23 Received 10 20 30 40 100
        Handled 5 15 25 35 80

        There would another row for each date, but there is the general idea. I don't need a total for the day since the 3 items are not something that would be valuable summed together.

        I could probably step through each key and assign it a location in the spreadsheet which could work just fine since it is the column. I am just not sure how to step a hash for a single key.

        20070724 Janitored by Corion: Removed superfluous <table> element

[reply]
[d/l]

In Section Seekers of Perl Wisdom