#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0

    SQL Database to Perl Script


    I have a 'transaction' table created in SQL like this:

    TranID AccNum Type Amount ChequeNo DDNo
    657520 0181432 Debit 16000 465774
    657524 0181432 Debit 13000 569086
    657538 0181432 Credit 11000
    657548 0181432 Credit 15500
    657519 0181432 Debit 12000
    657523 0181432 Credit 11000
    657529 0181433 Debit 15000 466777
    657539 0181433 Credit 10000
    657541 0181433 Debit 12000
    657525 0181433 Debit 15000 569999
    657533 0181433 Credit 12500

    Query data from transaction table and calculate total amount debited by cheque, dd and by cash for each account.
    Script in Perl is:
    #!/usr/bin/perl
    use DBI;
    use strict;
    use warnings;
    $dbh = DBI->connect('dbi:database','prithvi','prithvi') or die "Couldn't connect";
    my $sth = $dbh->prepare("SQL QUERY");
    $sth->execute;
    map {print "<td>$_</td>"}qw(Account Number-ChequeDeposit-DDDeposit-CashDeposit);
    print "<br/>";
    while( my @fields= $sth->fetchrow_array)
    {
    my $row = join ('-',@fields);
    print "$row\n";
    }
    $sth->finish;
    $dbh->disconnect;

    I just want a SINGLE SQL QUERY or a SUBQUERY which finds the total amount debited by cheque, dd and by cash for each account.
    Please help.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    832
    Rep Power
    496
    Hmm, this looks like a question about SQL, not Perl isn't it?
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    This sql statement will give you the total amount of 'Debit' rows by accnum, chequeno, ddno:
    Code:
    select accnum, chequeno, ddno, sum( amount ) as total_debit_amount
     from  schema.table
    where  type  =  'Debit'
    group by accnum, chequeno, ddno

IMN logo majestic logo threadwatch logo seochat tools logo