Hi,

I'm doing a query on a DBase table which should return a couple of
records.

        "select * from table where field like '%'"

This works fine when there is a value in "field".
If "field" is not filled these few records are missing from the result
set.

If I try this stmt in a "real" SQL database this works better even for
not filled fields?

Why? Or better said why is it not possible to do such things in DBase?

Michael

On Tue, 13 Jul 1999 15:20:23 +0200, Michael Fritz

You can change the query to:
    select * from table where field is null or field like '%'

Jan

On Tue, 13 Jul 1999 15:20:23 +0200, Michael Fritz

Michael,

My other reply was probably a bit too short to be really helpful.  So
here's a more thorough analysis.

As my other reply demonstrates, an empty string value is considered a
Null value by local SQL.  This is because there is no way in dBase to
make a difference between an empty string and a Null value for a
string field (in contrast with Logical, Numeric and Date fields).

If you do a comparison in SQL with a field containing a Null value,
the result of the comparison is always Null.  The only exceptions are
the special comparison statements 'is null' and 'is not null'.

So far for the 'why'-part of your question.  For a solution, I would
really need more information, but I can make some guesses.

I suppose the query contains actually a parameter instead of the
literal '%'-value?  In that case, you probably want the user to be
able to provide a wildcard search key.  If he types '%', you will want
all records displayed...

That can be achieved by changing the query to:
   select * from table
   where field like :Key or '%' = :Key

Hope this helps (better),

Jan