It's been quite a while since I've done any significant database work in perl, needless to say I'm a bit rusty both with the SQL side as well as the perl data structure side.

I need to look for certain records while summarizing some others basically I need all records by voucher_number and procedure_code, then walk through and take the records that have the right transaction_type.

my $sql = qq/select TOP 500 "vwGenSvcInfo"."Voucher_Number",
        "vwGenPatInfo"."Patient_Number", 
        "vwGenPatInfo"."Patient_First_Name", 
        "vwGenPatInfo"."Patient_Last_Name", 
        "vwGenSvcInfo"."Service_Date_From", 
        "vwGenSvcInfo"."Procedure_Code",
                "vwGenSvcPmtInfo"."Transaction_Type",
        "vwGenSvcPmtInfo"."Transfer_To_Carrier_Abbr"
from ("Ntier_Training"."PM"."vwGenSvcPmtInfo" "vwGenSvcPmtInfo" 
        INNER JOIN "Ntier_Training"."PM"."vwGenSvcInfo" "vwGenSvcInfo"
+ ON "vwGenSvcPmtInfo"."Service_ID"="vwGenSvcInfo"."Service_ID") 
        INNER JOIN "Ntier_Training"."PM"."vwGenPatInfo" "vwGenPatInfo"
+ ON "vwGenSvcInfo"."Patient_ID"="vwGenPatInfo"."Patient_ID"
order by Voucher_Number
/;

my $dbh = DBI->connect('DBI:ODBC:DATABOX');
my $sth = $dbh->prepare($sql);
$sth->execute();
my %pats_hoh;

while (my $pats = $sth->fetchrow_hashref) {
    #next unless exists($pats->{'Transaction_Type'});
    my $voucher = $pats->{'Voucher_Number'};
my $proc_code = $pats->{'Procedure_Code'};
    $pats_hoh{$voucher}{$proc_code}{Patient_Number} = $pats->{'Patient
+_Number'};
    $pats_hoh{$voucher}{$proc_code}{Voucher_Number} = $pats->{'Voucher
+_Number'};
    $pats_hoh{$voucher}{$proc_code}{Patient_Last_Name} = $pats->{'Pati
+ent_Last_Name'};
    $pats_hoh{$voucher}{$proc_code}{Transaction_Type} = $pats->{'Trans
+action_Type'};

[download]

What I have now above crushes too much of the data. I need to see all the records for a couche_number and procedure_code, as it is now I only see one for each unique voucher/procedure. Maybe a hash is the wrong structure.

Any thoughts? - Help!

Thanks!!!

Sweetblood

In reply to Suggestions for handling a complex set of data by sweetblood

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.