Previous Article | matchIT SQL Index | Next Article |
TVF Functionality is not part of the standard matchIT SQL license, it is licensed separately and requires you have the base version of matchIT SQL to have access to the Lookup Module. The Singleaddresslookup TVF's require you have matchIT SQL and the addressing module before they can be added to your license.
5.1.1 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. |
5.1.2 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).
5.1.3 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>
5.1.4 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.
5.1.5 mfn_SingleRecordMatchEx2
As the mfn_SingleRecordMatchEx TVF, except that its input is an XML query string (see section above).
5.1.6 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 |
|
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” />’)
5.1.7 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.
5.1.8 mfn_SingleAddressLookupExUS
As the mfn_SingleAddressLookupUS TVF, except that all available columns are output.
Previous Article | matchIT SQL Index | Next Article |