Help adding negative strings converted to numbers???

[Apollo6]

I am reporting totals from a Pervasive database. The column in the database is actually a string. What is occuring is that if I have five records with a value of 5.25 in each record but one of the records is -5.25, the result I pull out is showing $26.25 instead of $15.75. Is appears it is converting and adding the strings to an absolute value. I've posted the code below but was curious if anyone had suggestions on how to handle this. Thanks.


//Return DEDUCTION records for year to date...
$s_query_deduction_ytd = "SELECT UPCHKD.EARNDED AS TYPE, Sum(UPCHKD.EEXTEND) AS TOTAL, UPCHKGRP.DESCRIPTION
FROM
(UPEMPL INNER JOIN UPCHKH ON UPEMPL.EMPLOYEE = UPCHKH.EMPLOYEE) INNER JOIN (UPCHKD INNER JOIN UPCHKGRP ON UPCHKD.EARNDED = UPCHKGRP.EARNDED) ON (UPCHKH.PEREND = UPCHKD.PEREND) AND (UPCHKH.EMPLOYEE = UPCHKD.EMPLOYEE)
WHERE
UPEMPL.SSN = '$ss_num' AND
UPCHKH.TRANSDATE > $ytd_date AND
UPCHKH.TRANSDATE <= $mod_date AND
UPCHKD.EARNDED <> 'PRDIR' AND
UPCHKGRP.GROUPING = 2
GROUP BY UPEMPL.SSN, UPCHKD.EMPLOYEE, UPCHKD.EARNDED, UPCHKGRP.DESCRIPTION
ORDER BY UPCHKGRP.DESCRIPTION";

$s_result_deduction_ytd = odbc_exec($connect_soinc, $s_query_deduction_ytd);

$total = odbc_result($s_result_deduction_ytd, 2);
$c_total = '$' . number_format($total, 2, '.', ',');

 

[DRJ478]

Obvious question:
Why do you store numerical data in a string column?

 

[Apollo6]

DRJ478-
Obvious answer: poor database design...
It would be so much easier if it wasn't but it is our Accounting package which comes out of the box in that format, which we cannot modify. Regardless, I still have to try and figure out a way to handle it.

I was thinking possibly something along the lines... Before actually summing my total column in the first query, maybe build an array of all records returned. Then do a conversion somehow on the problem field, then summing the total field in the array? Any ideas on that? Thanks.

 

[Apollo6]

What about the possiblities of using Type_Casting??? Not sure if that can be applied on an array. Still reading up on that...

 

[DRJ478]

A quick look into the Pervasive SQL manual shows me that there is a CONVERT function that can be used to convert string into numerical. You should be able to use that to construct the appropriate SQL statement.

http://www.pervasive.com/support/technical/psql2k/sqlref.pdf

Page 211

 

[Apollo6]

Thanks, I found the CONVERT function and tried various data types but still no luck. Even better, I mis-spoke when stating the field was a string format, it appears that way when linking the table via ODBC in MSAccess. When looking at the actual table through Pervasive Control Center, it is in fact a decimal format. Which has just blown me away as to why summing the field is treating the negative numbers as absolute values. Thanks anyway, still looking/testing.