The LA Fox Developer Newsletter

April1998

SQL Substring Searches!

by Michael Meer

[Ed. Note: Michael Meer is the Vice President of Microcom-

puterEngineering Seivices, LLC, developers of highly-custom-

ized 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:

RecCount

Command A

Command B

60

19.94 seconds

15.52 seconds

5

3.31 seconds

6.84 seconds

So the question presents itself - Why use the syntax in com-

mand 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;

FROM myTable;

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

Key

Name

Table A:

A

Arnon

B

Tom

C

Michel

D

Scot

E

Paul

Fname

Table B:

A

GaI-Oz

B

O’Hare

C

Fournier

D

Becker

F

Elliot

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