<<
SET VAR vWhereClause = +
('Control#
LIKE'&.vSearchString&'OR ' + +
'Invoice#
LIKE'&.vSearchString&'OR ' + +
'OrderNumber
LIKE'&.vSearchString&'OR ' + +
'ShipZip
LIKE'&.vSearchString)
PROPERTY LV_Quotes
REFRESHLIST 'TRUE'
I have tried using WHERE Control# LIKE and
WHERE Control# Contains with the UNION SELECT but to no avail.
Since I have never used the UNION SELECT I
am sure I am not doing something correctly.
>>
Start by creating a VIEW as follows:
CREATE VIEW SearchView (DisplayColumn, SearchColumn) AS +
SELECT DisplayColumn, Control# FROM YourTable +
UNION SELECT DisplayColumn, Invoice# FROM YourTable +
UNION SELECT DisplayColumn, OrderNumber FROM YourTable +
UNION SELECT DisplayColumn, ShipZip FROM YourTable
where "DisplayColumn" is the name of the column you want to appear in the drop
down control and YourTable is the name of your table. You can either use the
word "SearchColumn" to name that second column, or some other name you prefer.
I'm assuming here that Control#, Invoice#, and OrderNumber are all TEXT
columns. If they are defined with different length, move the SELECT statements
around so that widest column is in the first SELECT statement.
Now, base your drop down on the view, not the original table. The WHERE clause
now becomes a simple "WHERE SearchColumn LIKE 'Value%'".
I think that R:Base's query optimizer is smart enough to optimize this query,
but there's a chance it won't be able to and the search speed you get from this
will be the same as the existing speed. If that proves to be so, you need to
get just a tiny bit fancier, basing the drop down on a TEMPORARY view and,
instead of recreating the WHERE clause each time, you need to recreate the
temporary view with the WHERE conditions built in to each SELECT. If that
turns out to be necessary, post back and we can help you do that.
--
Larry
