Interbase: conversion error from string ""

Question

I'm running IB2009 and I'm trying to count the number of records where a specific field is neither NULL nor empty:

SELECT COUNT(A.ID) FROM MYVIEW A
WHERE ((A.VARCHARFIELD1 IS NOT NULL) OR (A.VARCHARFIELD1 <> ''))

where MYVIEW is a VIEW, and MYVIEW.ID is an INTEGER, while MYVIEW.VARCHARFIELD1 is a VARCHAR(18).

I'm getting the error message

Error at line 1, conversion error from string ""

which I don't really understand, since when I drop the COUNT()-function, the query executes nicely. Do anyone know what I'm doing wrong? Thanks!

Answer 1

I don't see anything wrong with using COUNT() in this case. Although it shouldn't matter you might try dropping all the parentheses in your WHERE clause. You also might try using COUNT(*) instead of COUNT(A.ID) just to see if it gives you different results.

In any case, based on your description of the intent of the query I don't think that the query is going to do what you intended. If the query is supposed to return rows where the field is neither NULL nor empty I believe your WHERE clause should be

WHERE A.VARCHARFIELD1 IS NOT NULL AND
      A.VARCHARFIELD1 <> ''

With the 'OR' in there as originally written I think you'll get back every row in the table where A.VARCHARFIELD1 is NOT NULL, as the "field NOT NULL" predicate will allow in all non-NULL values, and since the two predicates are joined by an OR the second predicate won't matter.

I hope this helps.