Knowledge Base

FIX: "Syntax Error" with EVAL of ALL or LTRIM in SELECT-SQL

Article ID: 193079

Article Last Modified on 8/12/1999


APPLIES TO


This article was previously published under Q193079

SYMPTOMS

If you issue a SELECT SQL statement that contains a WHERE clause comparing a field value to an expression, which involves an ALLTRIM() or LTRIM() function that is passed to the EVALUATE() function, if the trim function trims a leading space or spaces the following error occurs when the SELECT statement is processed:
Syntax error.

RESOLUTION

As a workaround, trim the leading space or spaces by using STRTRAN() rather than ALLTRIM() or LTRIM().

The following example demonstrates the workaround:
   SELECT *, .t. FROM temp_table ;
      WHERE temp_table.cUsageTL = EVALUATE(STRTRAN("lcService" ;
      + (" 91")," ","")) ;
      INTO CURSOR Temp4
				

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. This bug has been corrected in Visual FoxPro 6.0.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Run the following code from a program (.PRG) file:
          CLOSE DATABASES ALL
          SET SAFETY OFF
    
          * Create table and index.
          CREATE table temp_table (cBTN c(2), cUsageTl c(10))
          INDEX on cUsageTl tag cUsageTl
    
          SET SAFETY ON
    
          * Insert some records.
          FOR lnj = 1 to 10
             INSERT into temp_table (cBTN, cUsageTl) values ("91", "Sp800/W")
             INSERT into temp_table (cBTN, cUsageTl) values ("91", "Sp800/X")
             INSERT into temp_table (cBTN, cUsageTl) values ("91", "Sp800/Y")
          ENDFOR
    
          * Initialize variables to Evaluate.
          lcService91="Sp800/W"
          lcService92="Sp800/X"
          lcService93="Sp800/Y"
    
          * Issue SELECT with trailing, no and leading space.
          SELECT *, .t. FROM temp_table WHERE temp_table.cUsageTl = ;
             EVALUATE("lcService"+ALLTRIM("91 ")) ;
             INTO CURSOR Temp2
    
          SELECT *, .t. FROM temp_table WHERE temp_table.cUsageTl = ;
             EVALUATE("lcService"+ALLTRIM("92")) ;
             INTO CURSOR Temp3
    
          * It fails with the leading space.
          SELECT *, .t. FROM temp_table WHERE temp_table.cUsageTl = ;
             EVALUATE("lcService"+ALLTRIM(" 93")) ;
             INTO CURSOR Temp4
    							
In Visual FoxPro 5.0, the third select fails with a "Syntax error". This works without error in Visual FoxPro 6.0.

Demonstration of Workaround

Replace the third select statement in the preceding program example with the following code:
   SELECT *, .t. FROM temp_table ;
      WHERE temp_table.cUsageTL = EVALUATE(STRTRAN("lcService" ;
      + (" 91")," ","")) ;
      INTO CURSOR Temp4
					
RESULTS: After running the program, no error occurs, and the STRTRAN() function trims the leading space.

REFERENCES

(c) Microsoft Corporation 1998. All Rights Reserved. Contributions by Jim Saunders, Microsoft Corporation

Additional query words: kbVFp500abug kbVFp600fix kbSQL

Keywords: kbbug kbfix kbcode KB193079