Database - SQL Table to Perl Script

Question

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

TransactionID    Date           AccountNumber   Type    Amount  
657520           02-07-1999     016901581432    Debit   16000  
657524           02-07-1999     016901581432    Debit   13000  
657538           09-07-1999     016901581432    Credit  11000  
657548           18-07-1999     016901581432    Credit  15500  
657519           02-07-1999     016901581433    Debit   12000  
657523           02-07-1999     016901581433    Credit  11000  
657529           03-07-1999     016901581433    Debit   15000  
657539           10-07-1999     016901581433    Credit  10000  
657541           11-07-1999     016901581434    Debit   12000  
657525           03-07-1999     016901581434    Debit   15000  
657533           05-07-1999     016901581434    Credit  12500

I have to find the total debit amount and total credit amount for each account using database. My code is like this:

#!/usr/bin/perl
use DBI;
use strict;
use warnings;
print "content-type:text/html\n\n";
$dbh = DBI->connect('dbi:___','prithvi','*password*') or die "Couldn't connect";
my %trans;
my $tran = $dbh->prepare("SELECT * FROM `transaction` LIMIT 0 , 11");
$tran->execute;
while(my @row = $tran->fetchrow_hash) 
{
    my $tran = join ',', @row;
    $trans{$tran[2]}{$tran[3]} += $tran[4];
}
foreach my $acno(sort keys %trans) 
{
print "Total Amount deposited and total amount credited for Account Number $acno is Rs.$trans{$acno}{'Debit'} and Rs.$trans{$acno}{'Credit'}\n";
}
$tran->finish;
$dbh->disconnect;

Where am I going wrong?

I am not getting any error but I am not getting the proper output — user1708526
– user1708526, Commented Oct 3, 2012 at 6:44
Add use strict and use warnings , it might point to a problem. — RobEarl
– RobEarl, Commented Oct 3, 2012 at 7:36
A few more beginner tips: You don't need to print the content-type line if it is not in a CGI environment. You also haven't declared $dbh, which strict will tell you about. It's generally a good idea to name things like they are named in the documentation. Most people call their statement handles (the result of $dbh->prepare) $sth or $sth_something. — simbabque
– simbabque, Commented Oct 3, 2012 at 8:45
The edits you made to this question render the answers you received completely obsolete. Consider showing your original code instead? Or at least post the latest as an update at the bottom? This is bordering on too localized now, and may be closed as such. — user50049
– user50049, Commented Oct 3, 2012 at 15:10

RobEarl · Accepted Answer · 2012-10-03 07:59:19Z

1

If you add use strict and use warnings you'll probably get some feedback about needing to declare @tran in the code below:

while(my @row = $tran->fetchrow_hash) 
{
    my $tran = join ',', @row;
    $trans{$tran[2]}{$tran[3]} += $tran[4];
}

$tran is 657520,02-07-1999,016901581432,Debit,16000 when you try and use it as an array.

Use Data::Dumper to show what you put into %trans at the end:

use Data::Dumper;
print Dumper(\%trans);

Did you mean:

while(my @row = $tran->fetchrow_array) 
{
    $trans{$row[2]}{$row[3]} += $row[4];
}

edited Oct 3, 2012 at 7:59

answered Oct 3, 2012 at 7:53

RobEarl

7,9226 gold badges38 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1708526 Over a year ago

Oh.Yes.You are right.I got the output.Thank you so much everyone

Toon Krijthe · Accepted Answer · 2012-10-04 06:14:48Z

0

Use SQL to get this answer not code.

SELECT AccountNumber, Type, SUM(Amount) FROM transaction GROUP BY AccountNumber, Type;

edited Oct 4, 2012 at 6:14

Toon Krijthe

53.6k38 gold badges151 silver badges204 bronze badges

answered Oct 3, 2012 at 7:12

user1126070

5,0691 gold badge18 silver badges15 bronze badges

5 Comments

user1708526 Over a year ago

I want to get the output from Perl script itself. Thats why I am asking help.

user1708526 Over a year ago

I dont no whether its possible or no.

user1708526 Over a year ago

Can you please tell me what is the single SQL query for finding both Debit Amount and Credit Amount? Thanks in advance.

RobEarl Over a year ago

You should accept an answer and ask a new question. See the comments on this question. However, I've edited this answer to what you're asking.

user1708526 Over a year ago

Thank you so muc Mr.RobEarl. Finally I got the output as required from your help.

Collectives™ on Stack Overflow

Database - SQL Table to Perl Script

2 Answers 2

1 Comment

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related