macth() in where clause

[#1]

Hello,

i'm trying to load all record from a table where the type field matches either vNumber1 or vNumber2 using the folowing code:

set vNumber1 = 9624;

set vNumber2 = 9701;

LOAD

*;

SQL Select *

from Table1

where match(type,$(vNumber1 ),$(vNumber2 ));

however, this results in the following message:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: 'match' is not a recognized built-in function name.

SQL Select *

from Table1

where match(type,9624,9701)

Why is the match function not working here?

[marcus_sommer · Accepted solution]

Match() is a qv-function and will be not recognized from the database or the odbc-driver. You will need use a sql-version from match(), maybe IN or apply the where-clause to a load-statement:

LOAD * where match(type,$(vNumber1 ),$(vNumber2 ));

SQL Select * from Table1;

- Marcus

[#3]

This seems to be working! Thanks.

After considering your code, it seems like loading the entire table before filtering is an unnecessary load on the source DB.  I will therefore opt for direct filtering in the where Clause:

LOAD * ;

SQL Select * from Table1

where type = $(vNumber1 )

or type = $(vNumber2 ));