Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

Re^3: SQL Database Table to Perl Script

by marto (Chancellor)
on Oct 05, 2012 at 08:30 UTC ( #997411=note: print w/ replies, xml ) Need Help??


in reply to Re^2: SQL Database Table to Perl Script
in thread SQL Database Table to Perl Script

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


Comment on Re^3: SQL Database Table to Perl Script
Download Code
Re^4: SQL Database Table to Perl Script
by prithviraj (Novice) on Oct 05, 2012 at 09:33 UTC
    + 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.

      Thank you, I'm well aware how this site works with regards posting and displaying code. If you click the download link provided you get the code exactly as you have submited it to the site.

      This confusion aside, here's a couple of points:

      • You're ignorning the fact that I call your example with -c, meaning I'm compiling it only, not running it. I don't execute it. Your statement regards different databases is irrelivant. See perlrun, look for -c
      • You're either ignoring or don't understand the error messages I posted, which clearly show that the $dbh variable has not been declared as my $dbh. See strict.

      The example you posted DOES NOT WORK. You must be running code which is different to the code you posted.

Re^4: SQL Database Table to Perl Script
by prithviraj (Novice) on Oct 05, 2012 at 09:51 UTC
    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;
Re^4: SQL Database Table to Perl Script
by prithviraj (Novice) on Oct 05, 2012 at 09:53 UTC
    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.

      This is a basic SQL question, not a Perl question. I suggest you take the time to learn basic SQL, and for that matter Perl and HTML (note you have <br> tags after <td> tags). You've been given advice already which mentions what SQL functions to read up on, I won't repeat that. I suspect you've been given this task as a learning exercise (be it in an educational establishment, or on the job training/learning), and would suggest you treat it as such.

      Now can you give me the solution for this question please:

      If you're hungry and just want a fish to eat, I'll sell you one. If you want to learn to catch your own fish, that's a different situation.

      For your first question, you say you're already successfully getting the credit and debit values, so it looks to me like you'd get the account balance by subtracting one from the other. On the second question, it sounds like you want to group the results by whether the item has a CheckNo value, a DDNo value, or neither. It might be possible to do that with a single fancy SQL statement, but I'd probably just break my statement up into three, adding a WHERE clause to each, like these:

      WHERE CheckNo is not null WHERE DDNo is not null WHERE CheckNo is null AND DDNo is null

      That way your first statement gets the check values, your second gets the DD values, and your third gets the cash values. I assumed that those fields are null when they aren't filled in. If they default to empty strings, you'll need to adjust accordingly. Hope that helps.

      Aaron B.
      Available for small or large Perl jobs; see my home node.

Re^4: SQL Database Table to Perl Script
by Generoso (Vicar) on Oct 05, 2012 at 14:17 UTC

    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

      Sir.For the question below, I want to solve it with a single SQL query. I tried all possibilities but nothing is working. Please help: Query data from transaction table and calculate total amount debited by cheque, dd and by cash for each account.

        1) Query data from transaction table and calculate the total balance in each account

        Select columns from your table, group by "accnum". When "type" = 'credit', then add "amount"; when "type" = 'debit', subtract "amount". And then display the result as a column in your resultset.

        2) Query data from transaction table

        SELECT columns FROM transaction table

        and calculate total amount

        sum(...)

        debited by cheque,

        chequeno IS NOT NULL

        dd

        ddno IS NOT NULL

        and by cash

        chequeno IS NULL AND ddno IS NULL

        for each account.

        GROUP BY accnum

        For the rest, why not look here? And there.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (5)
As of 2014-07-26 11:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (175 votes), past polls