Using LIKE clause inside OPENQUERY

[dand11]

I'm trying to query a Linked server and need to use the LIKE clause. I'm using the following but I' getting an error that states: OLE DB provider "Advantage.OLEDB" for linked server "CODE3" returned message "Error 7200: AQE Error: State = 42000; NativeError = 2115; [Extended Systems][Advantage SQL Engine]Expected lexical element not found: USER There was a problem parsing the WHERE clause in your SELECT statement. -- Location of error in the SQL statement is: 62 Select STATLINK from STATUTE where UPPER(ST_STRING) LIKE UPPER('%394 467%')".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Select STATLINK from STATUTE where UPPER(ST_STRING) LIKE UPPER('%394 467%')" for execution against OLE DB provider "Advantage.OLEDB" for linked server "CODE3".

Select * from OPENQUERY(CODE3,'Select STATLINK from STATUTE where UPPER(ST_STRING) LIKE UPPER(''%394 467%'')')

 

[bborissov]

I am not familiar with Advantage syntax but why you use UPPER() when you compare digits?
How about:

Select * from OPENQUERY(CODE3,'Select STATLINK from STATUTE where ST_STRING LIKE ''%394 467%''')

or, if Advatage supports double quotes:

Select * from OPENQUERY(CODE3,'Select STATLINK from STATUTE where ST_STRING LIKE "%394 467%"')

Keep in mind that this may not work also.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[dand11]

You're right, I didn't need the Upper() function..copy paste mistake. But it still gives me the following error when I try to run your suggestio:

Select * from OPENQUERY(CODE3,'Select STATLINK from STATUTE where ST_STRING LIKE ''%394 467%''')

OLE DB provider "Advantage.OLEDB" for linked server "CODE3" returned message "Error 7200: AQE Error: State = 42000; NativeError = 2115; [Extended Systems][Advantage SQL Engine]Expected lexical element not found: USER There was a problem parsing the WHERE clause in your SELECT statement. -- Location of error in the SQL statement is: 51 Select STATLINK from STATUTE where ST_STRING LIKE "%394 467%"".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Select STATLINK from STATUTE where ST_STRING LIKE "%394 467%"" for execution against OLE DB provider "Advantage.OLEDB" for linked server "CODE3".

 

[bborissov]

Did you tried my second suggestion?
You should use Advatage syntax in your query.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[tav1035]

dand11,
Why a space between your numbers 394 467?
Are you looking for records with 394 and 467
Are you looking for records with 394 or 467

Select * from OPENQUERY(CODE3,'Select STATLINK from STATUTE where ( contains(ST_STRING,' %394% | %467% ') > 0'

tav