List:General Discussion« Previous MessageNext Message »
From:Jann Linder Date:May 1 1999 5:06pm
Subject:Re: [CGI] ERROR IN SQL Re: how do i make SQL do the calculations
and return the total.
View as plain text  
How come i get this error when running this query?

ERROR 1064: parse error near '(((rate * hours) * ((100-discount) * .01)) )
FROM
billable WHERE company = 'abc'

--
Jann Linder
Web Developer
Perl Programmer

Personal Site: http://www.jann.com/
CalendarPlus Site: http://www.calendarplus.com/


----------
>From: "Jann Linder" <jann@stripped>
>To: jimf@stripped
>Cc: cgi-list@stripped
>Subject: [CGI] SOLUTION Re: how do i make SQL do the calculations and return
the total.
>Date: Sat, May 1, 1999, 9:54 AM
>

> Thanks SO MUCH!  I will try it.
> --
> Jann Linder
> Web Developer
> Perl Programmer
>
> Personal Site: http://www.jann.com/
> CalendarPlus Site: http://www.calendarplus.com/
>
>
> ----------
>>From: Jim Faucette <jimf@stripped>
>>To: Jann Linder <jann@stripped>
>>Subject: Re: how do i make SQL do the calculations and return the total.
>>Date: Sat, May 1, 1999, 9:52 AM
>>
>
>> Jann Linder wrote:
>>>
>>> Hello All...
>>>
>>> I want to make SQL do all the work here of the calculation (there are many
>>> many entries in the table for the company code.  How do i convert the perl
>>> (inside the while loop below) to a SQL call which does the calculation?
>>>
>>> I have already immeasurably sped up the routine by using references...
>>>
>>> It has GOT to be faster to make MySQL do the calcs, right?
>>>
>>> any ideas?
>>> TIA
>>>
>>> Jann
>>>
>>> s[u]b get_billables{
>>>
>>>  # AC is the Account Code
>>>
>>>  my $AC = shift;
>>>
>>>  # open connection to database
>>>
>>>  my $totalb;
>>>
>>>  my $db_username = "zyx";
>>>
>>>  my $db_password = "xyz";
>>>
>>>  my $data_source = "dbi:mysql:database=billing;host=localhost";
>>>
>>>  my $dbh = DBI->connect($data_source, $db_username, $db_password) || die
>>> $DBI::errstr;
>>>
>>>  my $sqlstr=qq/SELECT rate, hours, discount FROM billable WHERE company =
>>> '$AC'/;
>>>
>>>  my $sth = $dbh ->prepare($sqlstr);
>>>
>>>  if (!$sth->execute) {
>>>   print "ERROR:" .$sth->errstr."\n";
>>>   die "ERROR:" .$sth->errstr."\n";
>>>   }
>>>
>>>  my @Column_References=(\$rate,\$hours,\$discount);
>>>
>>>  my $rv = $sth ->bind_columns(undef, @Column_References);
>>>
>>>  while ($sth->fetch) {
>>>
>>>   #$discount is the discount
>>>   # (in numbers such as 25, 50, 75 representing percentages)
>>>
>>>   $totalb += (($rate * $hours) * ((100-$discount) * .01));
>>
>> How about:
>> SELECT SUM(((rate * hours) * ((100-discount) * .01)))
>> FROM billable WHERE company = 'whatever';
>>
>>   jim...
>>
> -----------------------------------------------------------------
> To unsubscribe, send mail to "majordomo@stripped" with "unsubscribe cgi-list"
> in the body.
>
> The CGI Tips & Tricks website (and archive of the list) is located
> at http://perl.jann.com/
>
> cgi-list is hosted by Summit Communication Design
(http://www.summitdesign.net).
>
> Summit Communication Design is a full-service web hosting firm
> providing web design, cgi programming and inter/intra-net support.
> -----------------------------------------------------------------
> 
Thread
Re: [CGI] ERROR IN SQL Re: how do i make SQL do the calculationsand return the total.Jann Linder1 May