Beefy Boxes and Bandwidth Generously Provided by pair Networks Frank
Welcome to the Monastery
 
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 (Priest) 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 (Priest) 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 surveying the Monastery: (5)
As of 2014-04-19 02:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (475 votes), past polls