EXCELLENT exercise and suggestions Larry!
On a side note, I love when folks tell us how we should fix the problem as compared to just complaining about it.
Your comments and solutions are ALWAYS appreciated!
Very Best Regards,
Razzak.
At 07:21 AM 10/8/2003 -0700, Larry Lustig wrote:
The problem is that with ZERO set ON, every time an expression is encountered in code, NULL values of any numeric type are immediately converted to 0. So, if you are going to fix IFNULL, what behavior would you prefer to see?
For instance:
SET ZERO ON SET VAR vNullInt INT = NULL
and then:
(IFNULL(.vNullInt, 'Null', 'Non-null'))
should probably return 'Null'. However:
(IFNULL(.vNullInt*10, 'Null', 'Non-null'))
should return which one? I would say, probably 'Non-null', since the expression would certainly return a non-null value if used in code. And how about:
(IFNULL((.vNullInt), 'Null', 'Non-null'))
Does the presence of the parens around the .vNullInt turn the reference to a NULL value into an expression which should respect SET ZERO ON?
In other words, it's not immediately clear to me how the IFNULL function could ever "see" a NULL value in its first parameter if ZERO is ON.
One solution would be the addition of another function IFNULLVAR which would accept a Variable Name (as Text) and two arguments, and test the variable with that name for NULL. This would allow you to get around the expression evaluation. It wouldn't help you in your form problem, since the lookup would still be evaluated and converted to 0.
A work around might be changing your columns to default to zero and then not allowing NULLs. But I'm guessing that your REALLY do need to differentiate between the NULL values and the really 0 ones.
A final work-around would be as follows: Keep your tables as-is. Set ZERO OFF. Declare a view for each table that contains number values with all the real columns and additional values for each number-type field of the form:
CalcCol = (IFNULL(RealCol, 0, RealCol))
Base all your work on the single-table views (which should be editable). For forms and editing use the RealCol and for reports and calculations use the CalcCol.
For instance, if you have a table:
CREATE TABLE RawTestTable ( + IndivID INTEGER, + MinBalance REAL, + AverageRate REAL )
then create a view:
CREATE VIEW TestTable ( + IndivID, + MinBalance, + CalcMinBalance, + AverageRate, + CalcAverageRate ) AS + SELECT + IndivID, + MinBalance, + (IFNULL(MinBalance, 0, MinBalance)), + AverageRate, + (IFNULL(AverageRate, 0, AverageRate)) + FROM RawTestTable
-- Larry
At 02:44 PM 10/7/2003 -0700, Dennis McGrath wrote:
My personal preference is to fix IFNULL so it will always work. After all, even with ZERO ON, SELECT COUNT(*) FROM TableName WHERE ColumnName IS NULL works just fine, even on REAL values. That's logical. The documentation says ZERO ON makes calculations act as if nulls are zeros. Apparently, it affects a whole lot more, in an undocumented way.
