2 Replies Latest reply: Feb 18, 2015 3:16 AM by Erik Venema RSS

    macth() in where clause

    Erik Venema

      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?

        • Re: macth() in where clause
          Marcus Sommer

          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

            • Re: macth() in where clause
              Erik Venema

              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 ));