Howdy all.

Have a problem converting a Pdox QBE to SQL in that the results of the Pdox
QBE treats nulls as 0s as I have Treat blanks as zeros in the Database tab
of Preferences (tools, settings, preferences).

However, the sql query does not treat blanks as zeros and thus the balance
calculation from the SQL are off from the QBE calc. All table results
leading up to this query are a match between the QBE and sql results.

How may I include or haves zeros (0) used in the sql query. Or is the sql
conversion incorrect? In the sql conversion, initially only CAST the results
of the calculation instead of each field in the calculation being CAST.
Found that this resulted in incorrect values...

Both qbe and sql conversion are below. Using Pdox 10 sp3.

Thank you,
Rey

***************************

qs11 = query ; creates riv:statun0B.db

:PRIV:STATUN0A | P1Bal | P2Bal | P3Bal | P1Trans

:PRIV:STATUN0A | P2Trans | P3Trans

:PRIV:STATUN0A | UnitNumFK | AssnNumFK | TotBal

:PRIV:STATUN0A | TotTran |
EndQuery


***********************************

sqlqs11= SQL

SELECT UnitNumFK,
AssnNumFK,
TotBal CurrBal,
(CAST(P1Bal AS NUMERIC) - CAST(P1Trans AS NUMERIC)) P1Bal,
(CAST(P2Bal AS NUMERIC) - CAST(P2Trans AS NUMERIC)) P2Bal,
(CAST(P3Bal AS NUMERIC) - CAST(P3Trans AS NUMERIC)) P3Bal,
(CAST(TotBal AS NUMERIC) - CAST(TotTran AS NUMERIC)) TotBal
FROM STATUN0A

endSQL

Rey

If you want to treat nulls as zeroes, I would recommend not to allow nulls.
Eg use a default value = 0 as a validity check in the table structure.

Local Sql has very few functions, so it's hard to fix the null handling in
SQL to be as in QBE.

--
Bertil Isberg - CTECH
Paradox buglist:
online: http://web.comhem.se/~u82608896/

"Rey" <reycollazo (AT) cox (DOT) net> skrev i meddelandet
news:eaQ3g.174294$bm6.134697 (AT) fed1read04 (DOT) ..