The LA Fox Developer Newsletter

April1998

SQL Substring Searches!

by Michael Meer

[Ed. Note: Michael Meer is the Vice President of MicrocomputerEngineering Seivices, LLC, developers of highly-customized mission-critical apps written in FoxPro.)

A few days ago, I was browsing the help file for syntax on the

SQL statement. I was looking at the examples and discovered

a long hidden gem for the WHERE clause. This gem is the

LIKE operator. The following two lines of code are both valid and

correct commands:

A) SELECT FROM myTable WHERE ‘IKE’ $ cname

B) SELECT • FROM myTable WHERE cname LIKE ‘%IKE%’

Both of the commands will result in the same number of records. Command A uses the $ operator which is case- sensitive and is not Rushmore optimizable. The ‘LIKE’ syntax is at least partially Rushmore optimizable. What makes the ‘LIKE’ syntax cool is the ability to use the % symbol. In SQL- speak, the % symbol tells FoxPro to allow unlimited characters on whichever side of the search string the % is placed. Thus, if you place the % after the search string only, you get a quick search. (Example: ... WHERE cname LIKE “MIKE%” would be similar to ... WHERE cname = “MIKE”) Place the % symbol on both sides of the search string and you get a substring select. On small data sets, command A will be slightly raster than command B. On large data sets, command B appears to be faster. Below are the actual times of the above commands as tested on 51,000 records:

So the question presents itself - Why use the syntax in command B? The answer lies in where the syntax exists. If you are creating SQL statements on the fly and you wilt know if the data set is large or small, then you can use the syntax in command A. However, if you are creating local views (stored SQL statements), then you may want to consider the syntax in command B. Why? Image you create the following local parametenzed view:

CREATE SQL VIEW my View AS;

SELECT;

WHERE cname LIKE ?pcname

The parameter pcname must either exist in the environment as you open the view or FoxPro wilt ask you for a value. Now, image creating an input screen with a text box for the user to input a search name. If you use the syntax in command A, you have tied the user to always using a substring search. Not very Rushmore optimizable! But with the “LIKE” syntax, you can put

(Con’t, page 8)

Outer Joins

Reprinted from The Virtual FoxPro Users Group

Even if you are a novice FoxPro developer you probably heard the magic words “OUTER JOINs” and the fact that FoxPro! Visual FoxPro doesn’t support them. Well, with version 5.0 OUTER JOINs finally made it into the language. But what are these OUTER JOINs anyway?

Ok, SELECT - SQL is a very poweful command, you can use it to get a subset of records from a single table, you can also use it to get a subset from more than one table, this is where JOINs come into play.

There are four ways to JOIN two tables. To help me explain them, I’ll use the following data

SELECT A.Key,A.Name,B.FNAME from A xxx JOIN B ON A. Key==B.Key ORDER BY I where xxx - will be the JOIN we will use.

INNER JOIN - this is the way foxpro has been doing it joins up until Visual FoxPRo 5.0 and even in 5.0 this is the default way when you use INNER JOIN records that have a matching record (based on the join condition) in our example the end result will be

A Amon Gal-Oz

BTom O’Hare

C Michel Fournier

D Scot Becker

LEFT OUTER JOIN - with this you will have all the records from the first table (left table) and only matching records from the second table. In our example the end result will be

AArnon Gal-Oz

B Tom O’Hare

C Michel Fournier

D Scot Becker

E Paul

RIGHT OUTER JOIN - this is just the opposite of LEFT OUTER (Con’t, page 7)

Page 3