Re: SQL Database Table to Perl Script

[erix]

in reply to SQL Database Table to Perl Script

You say:

I am getting the exact output for this.

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

[prithviraj]

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.

[marto]

"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.

[prithviraj]

+ sign is displayed in 2 lines. When I did paste my program in the discussion box, it received like that. Take off that + sign and execute. You might be executing it in a different database. Put that database name and execute. You will get the o/p.

[prithviraj]

Ok....This program:

#!/usr/bin/perl
use DBI; 
use strict;
use warnings;
print "content-type:text/html\n\n";
my $dbh = DBI->connect('dbi:mysql:database:3306','prithvi','prithvi') 
+or die("Couldn't connect"); 
my $tran_cur = $dbh->prepare("SELECT AcNo, Type, SUM(Amount) FROM bank
+tran GROUP BY AcNo, Type");
$tran_cur->execute;
map {print "<td>$_</td>"}qw(Account Number-Type-Total Amount);
print "<br/>";
while( my @data = $tran_cur->fetchrow_array)
{
 my $rec = join ('-',@data);
 print "$rec<br/>";
}
$tran_cur->finish;
$dbh->disconnect;

[prithviraj]

Now can you give me the solution for this question please: Query data from transaction table and calculate total amount debited by cheque, dd and by cash for each account.

[Generoso]

Maybe this SQL will help.

 SELECT AccNum, max(date), Type, sum(Amount) FROM transaction GROUP BY
+ AccNum, Type
union
SELECT AccNum, max(date),'Balance', sum(if (type='Debit',-1*Amount,Amo
+unt)) FROM transaction GROUP BY AccNum;

SELECT * FROM transaction t where chequeno is not null or ddno is not 
+null;

And this has little to do with perl this is an datbase question