FoxPro error about a logical expression

Question

Let's say I'm working with a FoxPro table called Org, and among its many columns it has a column called 'comm_typ'. If I have Org currently selected, and I say:

SET FILTER TO comm_typ = 'A'

Pretty simple, right? And it works fine; no errors, and only records with a comm_typ value of 'A' get returned.

However, if I try using that same logical expression to pare down an APPEND FROM command, FoxPro doesn't like it. If I issue the following commands:

Use Org in 1
Use Temp in 2
Select Temp    
APPEND FROM Org for comm_typ = 'A'

I get this error on the APPEND FROM: 'You must use a logical expression with a FOR or WHILE clause.'

I've double- and triple-checked the expected syntax on the APPEND FROM command, I've verified that there are actually records with comm_typ values of 'A' to be appended.

EDIT: I figured it out.

All the columns that give me an error when plugged into an APPEND FROM...FOR... command all have one thing in common: they contain NULL values. Not just an empty field, an actual .NULL. is what the APPEND FROM is choking on. On my Org table I issued this command:

REPLACE comm_typ WITH '' FOR ISNULL(comm_typ)

I then tried the append from again, and it worked. Another possible workaround, rather than replacing all nulls with blanks, is to adjust the FOR clause to account for the nulls, like this:

APPEND FROM Org FOR comm_typ = 'A' .and. !ISNULL(comm_typ)

These nulls exist because my office is in the midst of a transition from FoxPro to SQL Server, and in some cases we have data bouncing back and forth between FoxPro and SQL Server. When an empty FoxPro field is mapped to SQL Server, it gets represented as a NULL, and when that gets dumped back into a FoxPro remote view, it is represented as .NULL.

As you found out, the reason the APPEND FROM command failed is because NULL = <Any Value> returns NULL. An alternative to the comm_typ = 'A' .and. !ISNULL(comm_typ) is NVL(comm_type, '') = 'A' — Frank Perez
Yes, I read a very insightful blog this morning on how nulls are handled in FoxPro, here: link I also came across the MSDN entry on the NVL function, very helpful! — ScottieByNature

DRapp DRapp · Accepted Answer · 2011-04-20T14:29:15

First, what you are doing doesn't make sense... other than trying to duplicate all records of type and put back into the original Org table... Is this for testing syntax of commands or what. If you have other purposes, let us know.

Your syntax would be correct otherwise... But to be sure, you could do a test like this

close tables 
use Org
select 0
use Org AGAIN alias OrgVersion2
append from Org for comm_typ = 'A'

From a bit more clarification, I think I MAY know what's up. Does your SECOND Table that you are pulling the records INTO have a column called "Comm_typ", if NOT, that's your problem. The 'FOR' clause is for the table you are WORKING WITH, not where its coming FROM. So... Ex:

Org Table Comm_typ, Fld1, Fld2, Fld3, Fld4

Second Table Fld1, Fld2, fld3, Fld4

and your "Second" table has focus via Select SECOND and then do Append from Org for Comm_Typ = 'A' will fail because the append from is basing the FROM of ITS table structure. Its almost like the records from ORG are ALL coming into this preliminary record holder of the SECOND table structure (which does NOT have the Comm_Typ column), THEN applies the FOR clause against this "temp record holder" and allows insert or throws it out. In this case, the column doesn't exist and is giving you a false error...

What you may want to do instead is...

*/ THIS applies the filtering to a separate cursor with criteria pre-limiting the records you want.

select * from org where comm_typ = 'A' into cursor C_MyTempExtraction readwrite

*/ NOW, append since you KNOW its a clean set of records.

select Second
Append from C_MyTempExtraction

FoxPro error about a logical expression

2 Answers