Dore,

I think I should explain more detailed... We have our customers, that want our data in specific format. So, firstly, we select row data from our tables with specific criteria and then using another application (XForm) transform our data into desired output format. We did this job in our old system and created these tables. Now we developed a new system (re-engineering) and need to test the difference. My work partner has her assistant, who performed this sort of testing. She reported the diference in one logical field. In old system it appears blank, in new - .f. For this particular field I used iif expression (thank Mike, I changed it to just somecondition).

Anyway, I asked our manager if it's important problem or not, and he answered me, that it's not.

But I want to thank you and Mike for helping me understand possible workaround.

Thanks.


>I'm a bit confused. I've experimented and it seems that a SELECT - SQL will retain the BLANK nature of a field from the FROM table(s). What do you mean to 'clean' a field if it is empty. Do you mean to BLANK it. Clearly, you wouldn't have to worry about character fields since BLANK and EMPTY for them is the same thing. Is there a problem with zero, as opposed to BLANK, in a numeric field? If the only issue is with logical fields you could just BLANK the logical fields but what would be your criteria? If the field is already BLANKed, then you don't need to BLANK it and if it's .F. then it has a value. Am I missing something?
>
>>Hi Dore,
>>
>>Actually, my problem was that we created our new system, it considers each extract job is three (sometimes two, though) steps job:
>>Query - perform actual selection from our Database
>>XForm - transform row data into customer output format
>>Report - perform report (could be SDF text file, printed report, etc.)
>>
>>My colleague's assistant compared results between our new sytem produced table and the old system produced table and found the difference I described. I'm not sure, that this difference is important, but I tried to resolve it.
>>
>>So, in general in XForm could be a flag, do we need to 'clean' all fields if empty. (BTW, I experinced with your example a little more, and found, that Integer fields always creates with 0 as default value and numeric - with blank...)
>>
>> I imagine this little program: (haven't tested)
>>

>>  * Table is open
>>  If NeedToBeCleaned
>>    local lnFields, i
>>    local array laFields[1]
>>    lnFields=afields(laFields)
>>    for i=1 to lnFields
>>     lcCmd= 'BLANK FIELDS '+laFields[i,1]+' FOR EMPTY('+laFields[i,1]+')'
>>     &lcCmd
>>    next
>>  endif
>>

,
>>
>>I'm afraid it will take long to execute... but I don't know, how to improve it..
>>
>>>Good point. If you want to get three states out of a logical field, make it NULL enabled and use .T., .F. and NULL as the three states. These are mutually exlusive. If you need a fourth state, you can use BLANK but it will take more work and you need to be wary of the displayed values, as you point out.
>>>
>>>>Hi Dore,
>>>>
>>>>I tested your example, it should be:
>>>>

>>>>CREATE CURSOR testing (somefield l null)
>>>>APPEND BLANK
>>>>BROW
>>>>DISPLAY
>>>>?ISBLANK(somefield) && .T.
>>>>REPLACE somefield WITH .F.
>>>>DISPLAY
>>>>BROW
>>>>?ISBLANK(somefield) && .F.
>>>>BLANK FIELD somefield
>>>>DISPLAY
>>>>?ISBLANK (somefield) && .T.
>>>>BROW
>>>>

>>>>
>>>>Note the difference between real field value and display representation...
>>>>
>>>>>>>>This is not so. Every logical field actually has three states, .T., .F. or BLANK. Blank can be test with ISBLANK() and can be set with the BLANK command (and is the default status for a newly appended record). If the logical field is NULL enabled then it can have four states, the three above as well as .NULL. which can be set with REPLACE ... WITH .NULL. and can be tested with ISNULL(). They are all mutually exclusive states.
>>>>>>>
>>>>>>>Well ok, I didnt consider .NULL. since I rarely use them. But you cant say blank and .f. are mutually exclusive states since blank logicals evaluate to false.
>>>>>>
>>>>>>Thanks for the reply.
>>>>>>
>>>>>>CREATE CURSOR testing (somefield l null)
>>>>>>APPEND BLANK
>>>>>>?ISBLANK somefield && .T.
>>>>>>REPLACE somefield WITH .F.
>>>>>>?ISBLANK somefield && .F.
>>>>>>BLANK somefield
>>>>>>?ISBLANK somefield && .T.
>>>>>>
>>>>>>Therefore BLANK and .F. are not the same. The point I was making was that you can use a logical field to store three states (four with NULL enabled). I was in error to say that the four states are mutually exclusive since both BLANK logical and .F. evaluate to NOT TRUE (which is not the case with .NULL. which neither evaluates to TRUE or NOT TRUE). You can, however, put a four state value into a logical field if you are willing to test for BLANK and NULL.
>>>>>
>>>>>Sorry. I meant "ISBLANK(somefield)" in my examples above not "ISBLANK somefield".