Skip to main content

mSQL - TVF's in mSQL

Lauren Wheeler
  • Updated

TVF Functionality is not part of the standard mSQL license, it is licensed separately and requires you have the base version of mSQL to have access to the Lookup Module. The Singleaddresslookup TVF's require you have mSQL and the addressing module before they can be added to your license.

 

msp_SingleRecordMatch

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string and table/column mappings.
  • Fullname
  • Company
  • Address1
  • Town
  • Region
  • Postcode

 

This procedure finds all records that match a generated record made up from the parameters supplied as arguments for the procedure (Namely full name, company, address1, town, region, postcode), in the specified data source in the specified configuration.

The following results are returned:

Column

Description

RecordID

Reference ID for the matching record within the specified datasource.

Level

The Level indicates the matching level(s) at which a match was found.  If it contains a 1 then the two records match at the Individual level; if 2, then Family level; if 4, then Household level; and if 8, then Business level.  Multiple levels are indicated by summing values – for example, 9 would indicate a match at both Individual and Business levels (1+8), and 15 a match at all four levels (1+2+4+8).

By default, the Level column is followed by the total score for the four matching levels.  These columns are fully configurable within a configuration file.  Component scores (for name, organization, address, etc.) can also be output for any level(s).

IndividualScore

Individual level score.

FamilyScore

Family level score.

HouseholdScore

Household level score.

BusinessScore

Business level score.

 


mfn_SingleRecordMatch

 

This TVF (table-valued function) is identical to the msp_SingleRecordMatch stored procedure, except that its output is a temporary table that can be queried using a SELECT statement – for example, SELECT * FROM mfn_SingleRecordMatch(arguments).

The output is as msp_SingleRecordMatch – i.e. the total score for the four matching levels (see section above).

 

mfn_SingleRecordMatch2

 

This TVF (table-valued function) is identical to the mfn_SingleRecordMatch TVF, except that its input is an XML query string.

The XML query string has the following general format:

<query>

    <inputs>

        <fieldName>value</fieldName>

        <fieldName>value</fieldName>

        <fieldName>value</fieldName>

        …

    </inputs>

</query>

 

Where fieldname is any valid matchIT input field. For example:

<query>

    <inputs>

        <fullName>g anderson</fullName>

        <organization>mobil oil canada</organization>

        <address1>1160-1124 aviation park</address1>

        <town>huntsville</town>

        <region>alabama</region>

    </inputs>

</query>

 

mfn_SingleRecordMatchEx

 

As the mfn_SingleRecordMatch TVF, except that all available score columns are output – i.e. the total score plus all component scores for each level.

 

mfn_SingleRecordMatchEx2

 

As the mfn_SingleRecordMatchEx TVF, except that its input is an XML query string (see section above).

 

mfn_SingleGenerateKeys

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this function is run.
  • Datasource ID – specifies the data source to be used within the configuration file.
  • XML – specifies one or more data items for which to generate keys, in an XML-formatted string.

 

This function generates the keys only for the data passed into the function, and outputs a table containing a single row – for example, SELECT * FROM dbo.mfn_SingleGenerateKeys(arguments).

 

The XML data must be specified in the format “<data attribute=”value” … />” where attribute must be one of the standard matchIT API field types:

fullName

jobTitle

postIn

prefix

address1-9

country

lastName

flatNo

deliveryPoint

firstNames

premise

telephone

initials

thoroughfare

fax

qualification

town

dateOfBirth

suffix

region

email

organization

postcode

customField1-9

department

postOut

 

 

The XML data can contain any number of attribute=value pairs.  Here’s an example:

SELECT * FROM dbo.mfn_SingleGenerateKeys(‘config’, ‘datasource’,
‘<data fullName=”John Smith” organization=”360Science” />’)


mfn_SingleAddressLookupUS

 

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains database connection string and table/column mappings.
  • Company
  • Street
  • Street2
  • Suite
  • Urbanization – only for Puerto Rican addresses
  • City
  • State
  • Zip – can contain the full 9-digit zip or just the first five digits
  • Plus4 – can be left blank if Zip contains a 9-digit zip

 

This TVF (table-valued function) is a single-record version of the GenerateCorrectedAddresses stored procedure that can be queried using a SELECT statement – for example, SELECT * FROM dbo.mfn_SingleAddressLookupUS(arguments).

Please refer to the msp_GenerateCorrectedAddresses stored procedure, section Error! Reference source not found. here.

 

mfn_SingleAddressLookupExUS 

 

As the mfn_SingleAddressLookupUS TVF, except that all available columns are output.