in reply to Re: Merging Results from 3 different queries
in thread Merging Results from 3 different queries

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.

Replies are listed 'Best First'.
Re^3: Merging Results from 3 different queries
by BrowserUk (Patriarch) on Jul 24, 2007 at 17:46 UTC
[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]

        Try this. It won't produce exactly the output you are after, but it should show you how to access the various datasets so that you can manipulate them to your requirements. 

        # Get an arbitrary top level key and use it to
# create an ordered array of the sub hash keys
my $anyKey = keys %{ $hashref1 };
my @sortedKeys = sort keys %{ $hashref1->{ $anyKey }  };

for my $id ( sort keys %{ $hashref1 } ) {
  
    # extract an array of *ordered* results for this key from each has
+h
    my @results1 = @{ $hashref1->{ $id } }{ @sortedKeys };
    my @results2 = @{ $hashref2->{ $id } }{ @sortedKeys };
    my @results3 = @{ $hashref3->{ $id } }{ @sortedKeys };

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

        [download]

        If, for example, you want to have the columns in some order other than alpha-sorted, just hard code the keys array. Ie. Replace the sort (first two lines of code) with: 

        # Get an arbitrary top level key and use it to
# create an ordered array of the sub hash keys
# my $anyKey = keys %{ $hashref1 };
# my @sortedKeys = sort keys %{ $hashref1->{ $anyKey }  };

# Create an array of keys in the order you want them
{
    ## Suppress 'Possible attempt to put comments in qw() list at ...'
    ## because of the key: '#'
    no warnings 'qw'; 

    my @sortedKeys = qw[
        Cardiology 
        Neurology 
        All_sum 
        call_date 
        Gastroenterology 
        General 
        #
        Radiology 
    ];
}

        [download]

        Just put the column names in whichever order you wish them to appear. Also, omit any that you don't want.

        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.
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom