Not sure this will help but I always put a space on both sides of the &
----- Original Message -----
From: Jim Belisle
To: RBASE-L Mailing List
Sent: Tuesday, January 26, 2010 9:31 PM
Subject: [RBASE-L] - Re: ON KEY PRESS
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
