Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
>Try...
>SELECT * FROM MainCase WHERE Office + CaseNo Like "198-0000%"
>
>or
>
>SELECT * FROM MainCase WHERE left(Office + CaseNo,8) = "198-0000"
Bob,
Thanks for your reply! I finally figured out the problem after spending much of the day on this. It has to do with SET ANSI ON|OFF. When running an SQL SELECT in VFP, SET ANSI is OFF, while the default for the ODBC drive is ON. This setting determines how SQL comparisons are performed, similar to SET EXACT.
Anyway once I did a oConn.Execute("SET ANSI OFF"), the query worked! I should mention that there is a bug in the previous version of this drive where this setting cannot be turned OFF. (See MS KB Article Q169155.)
Although your solution works, the table is huge, and I cannot create an index for LEFT(...). It appears as though the LIKE clause uses the index (Office + CaseNo); therefore, it works too! This is nice to know.
Thanks again!
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement