Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

SQL Database Table to Perl Script

by prithviraj (Novice)
on Oct 05, 2012 at 04:05 UTC ( #997371=perlquestion: print w/ replies, xml ) Need Help??
prithviraj has asked for the wisdom of the Perl Monks concerning the following question:

I have a 'transaction' table created in SQL like this:
TranID Date AccNum Type Amount ChequeNo DDNo 657520 02-07-1999 0181432 Debit 16000 465774 657524 02-07-1999 0181432 Debit 13000 569086 657538 09-07-1999 0181432 Credit 11000 657548 18-07-1999 0181432 Credit 15500 657519 02-07-1999 0181432 Debit 12000 657523 02-07-1999 0181432 Credit 11000 657529 03-07-1999 0181432 Debit 15000 466777 657539 10-07-1999 0181432 Credit 10000 657541 11-07-1999 0181432 Debit 12000 657525 03-07-1999 0181432 Debit 15000 569999 657533 05-07-1999 0181432 Credit 12500
The question is: Query data from transaction table and Calculate the total debit amount and total credit amount for each account. My script is like this:
#!/usr/bin/perl use DBI; use strict; use warnings; $dbh = DBI->connect('dbi:database','prithvi','prithvi') or die "Couldn +'t connect"; my $tran_cur = $dbh->prepare("SELECT AccountNumber, Type, SUM(Amount) +FROM transaction GROUP BY AccountNumber, Type;"); $tran_cur->execute; print "<table><tr>"; map {print "<td>$_</td>"}qw(Account Number-Total Debit Amount); print "<br/>"; print "</tr>"; print "</table>"; while( my @data = $tran_cur->fetchrow_array) { my $rec = join ('-',@data); print "$rec\n"; } $dbh->disconnect;
I am getting the exact output for this. But I have two more questions.
1) Query data from transaction table and calculate the total balance +in each account 2) Query data from transaction table and calculate total amount debite +d by cheque, dd and by cash for each account.
How can I do this? I just have to replace the SQL query in the above script for the 2 new questions. Please help. Thanks in advance

Comment on SQL Database Table to Perl Script
Select or Download Code
Re: SQL Database Table to Perl Script
by james2vegas (Chaplain) on Oct 05, 2012 at 05:23 UTC
    Just include in your SELECT and GROUP BY your additional fields.
      What are the additional fields Sir? Please help with the full SQL query.
        I do not know the additional fields (how would I). They would be in your schema.

        prithviraj:

        You'll want something like:

        -- compute the totals for each account: select AccNum, sum(DB_amt) ttl_DB_amt, sum(CR_amt) ttl_CR_amt from ( -- transform the table from (accnum, type, amount) into -- (accnum, cr_amt, db_amt) select accnum, case when type='Credit' then Amount else 0 end CR_amt, case when type='Debit' then Amount else 0 end DB_amt from YOUR_TABLE_NAME where CHOOSE_WHICH_ACCOUNT(S)_YOU_WANT ) group by AccNum

        The inner select converts your table into a simpler view of account number, credit amount and debit amount. The outer select generates the totals for the simple view.

        Update: For your other questions, simply make the appropriate view transformations and accumulate the totals as above.

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

Re: SQL Database Table to Perl Script
by Polyglot (Monk) on Oct 05, 2012 at 05:56 UTC
    This is really an SQL question. Are you using MySQL? If so, they have some helpful pages online, e.g. HERE for how to construct your queries (though I must say there's a bit of a learning curve to figure out their "help"). Whichever database you're using should be able to do the calculation you need right from the query.

    Blessings,

    ~Polyglot~

Re: SQL Database Table to Perl Script
by erix (Vicar) on Oct 05, 2012 at 07:44 UTC

    You say:

    I am getting the exact output for this.

    But how is that possible when the script doesn't compile?

      Sir...The script what I showed compiles. I want the answer for the 2 new questions in the same method shown in the script. That means for the 2 new questions, I just have to change the SQL query in the script shown. I want your help for the query. Thats it.

        "Sir...The script what I showed compiles. "

        Nope:

        C:\>perl -c 997371.pl Global symbol "$dbh" requires explicit package name at 997371.pl line +5. Global symbol "$dbh" requires explicit package name at 997371.pl line +6. Global symbol "$dbh" requires explicit package name at 997371.pl line +18. 997371.pl had compilation errors.
Mysql reply
by Generoso (Vicar) on Oct 14, 2012 at 04:10 UTC

    Maybe this is what you are looking for.

    !/usr/bin/perl use DBI; use strict; use warnings; my $dbh = DBI->connect('dbi:mysql:database','user','password') or die +"Couldn't connect"; my @sql =(); $sql[0] = qq[SELECT AccNum as Account, max(date) as Date, Type, count( +*) as Total_No, sum(Amount) as Amount FROM transaction GROUP BY AccNum, Type union SELECT AccNum, max(date),'Balance', count(*), sum(if(type='Debit',-1*A +mount,Amount)) FROM transaction GROUP BY AccNum;]; $sql[1] = qq[SELECT AccNum as Account, max(date) as Date, Type, 'CHEQU +ENO' as Subtype, count(*) as Total_No, sum(Amount) as Amount FROM transaction where chequeno is not null GROUP BY AccNum, Type union SELECT AccNum as Account, max(date) as Date, Type, 'DDNO' as Subtype, +count(*) as Total_No, sum(Amount) as Amount FROM transaction where DDNO is not null GROUP BY AccNum, Type;]; $sql[2] = qq[desc transaction;]; open (FH, ">".$ENV{"TMP"}."\\mytmp.html") || die "Cannot open temporar +y file: $!\n"; print FH '<HTML><Body>'; foreach (@sql) { my $tran_cur = $dbh->prepare($_); $tran_cur->execute; print FH '<table border=1 cellspacing=0 cellpadding=2><tr>'; map {print FH "<td>$_</td>"}@{$tran_cur->{NAME}}; print FH "</tr><br/>"; while( my @data = $tran_cur->fetchrow_array) { print FH "<tr>"; map {print FH "<td>$_</td>"}@data; print FH "</tr>"; } print FH "</table>"; } print FH "</Body></HTML>\n"; my $url = "file://".$ENV{"TMP"}."\\mytmp.html"; my $commandline = qq{start "$url" "$url"}; system($commandline) == 0 or die qq{Couldn't launch '$commandline': $!/$?}; close FH; $dbh->disconnect; sleep(5); unlink $ENV{"TMP"}."\\mytmp.html" or warn "Could not unlink ".$ENV{"TM +P"}."\\mytmp.html: $!";

    Result:


    AccountDateTypeTotal_NoAmount
    1814321999-07-18Credit560000.00
    1814321999-07-11Debit683000.00
    1814321999-07-18Balance11-23000.00

    AccountDateTypeSubtypeTotal_NoAmount
    1814321999-07-03DebitCHEQUENO231000.00
    1814321999-07-03DebitDDNO228000.00

    FieldTypeNullKeyDefaultExtra
    TranIDint(10) unsignedNOPRI
    DatedateNO
    AccNumint(10) unsignedNO
    Typeenum('Credit','Debit')NO
    Amountdecimal(10,2)NO
    ChequeNoint(10) unsignedYES
    DDNoint(10) unsignedYES

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://997371]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (6)
As of 2014-11-27 03:17 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My preferred Perl binaries come from:














    Results (178 votes), past polls