Latest version 7.6. I have PK on the Control# and indexes on the three
other columns.
Just to review, the var I use in the field is vkeyword.
The original code is
IF (SLEN (.vKeyword )) < 2 THEN
GOTO Done
ENDIF
SET VAR vQuotes = (CVAL('QUOTES'))
SET VAR vMany = (CVAL('MANY'))
-- SET VAR vSingle = (CVAL('SINGLE'))
SET VAR vSearchString TEXT = NULL
SET VAR vWhereClause TEXT = NULL
SET VAR vSearchString = (.vQuotes+.vKeyword+.vMany+.vQuotes)
-- Define Where Clause
SET VAR vWhereClause = +
('Control# LIKE'&.vSearchString&'OR ' + +
'Invoice# LIKE'&.vSearchString&'OR ' + +
'OrderNumber LIKE'&.vSearchString&'OR ' + +
'ShipZip LIKE'&.vSearchString)
PROPERTY LV_Quotes REFRESHLIST 'TRUE'
LABEL Done
CLEAR VAR iv%
RETURN
I am still trying to get the OKP to work properly (speedily) but am no
closer. I tried using the UNION SELECT clause but get a column type
mismatch whenever I use it. At the R prompt if I use a portion of the
command without the UNON SELECT it works (If there is information that
matches).
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.
I have read the help and that gives me no answers either.
Jim
________________________________
From: [email protected] [mailto:[email protected]] On Behalf Of Lawrence
Lustig
Sent: Monday, January 11, 2010 1:08 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Re: ON KEY PRESS
<<
SET VAR vWhereClause = +
('Control# LIKE'&.vSearchString&'OR ' + +
'DistName LIKE'&.vSearchString&'OR ' + +
'ShipState LIKE'&.vSearchString)
Could you show me exactly how you would use a UNION to do that? I can't
figure out the syntax.
>>
Something like this:
SELECT Col1, Col2, Col3, Control#, DistName, ShipState FROM TableName
WHERE Control# LIKE 'Thing%' +
UNION +
SELECT Col1, Col2, Col3, Control#, DistName, ShipState FROM TableName
WHERE DistName LIKE 'Thing%' +
UNION +
SELECT Col1, Col2, Col3, Control#, DistName, ShipState FROM TableName
WHERE ShipState LIKE 'Thing%'
Assuming that Control#, DistName, and ShipState are all indexed, and
that your LIKE term does not start with a wildcard, this will execute
three indexed queries and combine the results. If the same row is
returned by more than one query, it will appear only once in the result
set. If you want it to appear for each query it matches, use UNION ALL
in place of UNION.
--
Larr
