zero values in DBase

otherwise, person_id might accept null values and the value is null

if so, then you need to do this

Select *
 from TableName
 where person_id = 0
or isnull(person_id)

Thanks for answer,but is null function doesn't work.
thanks

Lana

sorry (foxpro function)  I think this will work with dbase

Select *
 from TableName
 where person_id = 0
or person_id = null

so, you are sure there are person_id fields that have 0 in them?

old FoxPro will consider 0 and empty numeric field the same. In later VFP you can check for not null values

Select * from TableName where person_id = 0 AND NOT ISNULL(person_id)

dBASE3, I believe, considers a numeric field as zero, whether you put any data in it or not.

I don't have dBASE3 around here to look at any other way to suppress those blank values that equate to zero.  The closest I have is FoxPro 2.6a and that actually has a function called ISBLANK().  If that exists in dBASE3 (please check your Help file to verify), your query might look like this to suppress those invisible zero values and get the result you want:

Select *
 from TableName
 where person_id = 0 .AND. NOT ISBLANK()

dBase3 didn't support nulls and 'null' fields are only differantiated by VFP driver's ISBLANK() function provided field 'was never changed'. Internally numerics are stored as plain strings. Checking for IsNull() or person_id = .null. is meaningless with a dBaseIII file.

select * from TableName where person_id = 0

works for fields that are ever populated or not. In dBaseIII there were no function that'd find out a never populated field. IOW :

person_id = 0
empty(person_id)

are equally well.
If you check with foxpro :

person_id = 0
empty(person_id)

gives the same result.
IsBlank(person_id) would be .t. only for records that person_id were never populated by any means (those you see empty when you do a browse). However you shouldn't trust IsBlank() on numeric fields. The results are unpredictable compared to today's integer,double etc fields.

There is another workaround. If you put a -1 in a numeric field meaning the value is not entered or not available, you can query on it like this "field >= 0".

Try following comands to make sure if any record exists in with 0 values or not

USE TableName
count for person_id = 0 to x
? x


if it displays a 0 that means you dont have any 0 values in person id

personid=0    &    empty(personid)   returns the same in dBase3

Ghunaima,
How does that command differ from proposed ways ? You mean count is more dependable in some way ?

set deleted off
select * from TableName where person_id = 0 && Returns empty resultset
select cnt(*) from TableName where person_id = 0

locate for personid = 0
? found() && or eof()

You mean one of these might return a wrong result while count doesn't ? Actually count respects to filters and when using it you should also take that into account, no?
 

cbasoz,
I agree with you that result of all these commands is same but when you use Select statements in dBase the sometimes act very odd so I just wanted lana123 to make sure that she/he is getting correct results

I see. It has been many many years since I last used dBaseIII+, I even don't remember if it supported SQL.