> When comparing text columns to text variables you can actually > get away with "column = .var" when EQNULL is ON, but when using > non-text columns like INTEGERs, R:BASE is not as forgiving. If > you are specifically looking for NULL values you should use a > where clause with "column IS NULL". If you are using an INTEGER > variable which might be NULL, then you need to put parentheses > around the variable like "column = (.var)".
I've been struggling with this issue myself, and it seems to me the following is true: If you want to compare an INTEGER column to a variable that may or may not contain NULL, there is no way to do so with NULL set to ' '. 1. If EQNULL is set to OFF, the comparison is not valid, as you would expect. 2. If EQNULL is set ON and you use IntegerCol = .vMaybeNullVariable then the variable is converted to a blank and you get an error message about an invalid right side of the comparison. 3. If EQNULL is set ON and you use IntegerCol = (.vMaybeNullVariable) then the variable is converted to 0 (because the parens turn it into an expression, and because of the EQNULL setting the NULL is treated as a 0). Of these, the one I expect to work is #2, since you have a plain, non-expression variable containing NULL and an EQNULL setting of ON. It seems odd to me that R:Base is treating it like a blank space (the behavior you would expect with &vMaybeNullVariable rather than .vMaybeNullVariable). So it seems to me that you need to do one of two things: 1. Set NULL to '-0-' or some other value before executing the command (not a great option if the command is BROWSE or PRINT and you prefer the NULLs to appear as blanks). 2. Add program code to reconfigure the WHERE clause by looking at each possible column comparison, checking for NULL, and building a different WHERE clause with IS NULL comparisons. I'm really hoping for some way to do reliable NULL comparisons using EQUALS with NULL set to ' ', so if someone can explain how to handle it, I would be most appreciative. -- Larry
