Reference Guide

Reference Guide#

2-Step Configuration #

Follow the instruction to get the app and details about installation in Native App Installation.

To observe and troubleshoot app behavior, you can enable Logging and Event Tracing for your account and share the app logs with us. For more information, please check Snowflake Documentations below:

Stored Procedures #

VERIFY_SINGLE_ADDRESS #

The VERIFY_SINGLE_ADDRESS procedure validates and standardizes individual address using the SmartMover. It returns the verification results in JSON format or optional output table, supporting optional fields for flexible and accurate address validation.

Important

This feature is only for testing purposes or for contributing to a list of 100+ records (utilizing the same JobID) as per your SmartMover/PAF agreement. Learn more about inputs like JobID here.

There are 2 ways to verify an address in Native App SmartMover USA for Snowflake:

Fill in the Verification Form on the Native App interface.
Manually run a Snowflake SQL script to call the stored procedure.

Verification Form #

In your Snowflake account, select Data Products > Apps > SMARTMOVER > Menu > Verify Single Address > Try It Now > Verification Form.

Input Example #

Enter the License Key and address you want to verify.
Select Verify.
Result will be displayed on the left sidebar.

Output Examples #

Some common output examples are shown below.

Verify a single address

Result will be displayed in JSON format.

../../_images/Snowflake-SmartMover-USA-Stored-Procedures-Verify-Single-Try-Now-Result.png

Verify a single address to an output table with default output fields

You can choose to insert the result to an output table for later use with our Default Output Fields.

../../_images/Snowflake-SmartMover-USA-Stored-Procedures-Verify-Single-Try-Now-Default.png

Verify a single address with selected output table fields

You can choose which fields to be included in the output table.

../../_images/Snowflake-SmartMover-USA-Stored-Procedures-Verify-Single-Try-Now-Selected-Fields.png

Verify a single address with ‘ALL’ output table fields

You can choose to have All fields to be included in the output table.

../../_images/Snowflake-SmartMover-USA-Stored-Procedures-Verify-Single-Try-Now-All-Fields.png

SQL Script #

Manually call our procedures using a Snowflake SQL script.

Setup and Sample Script #

Select Projects > Worksheets on the left panel in your Snowflake account.

You can find the installed application database in Projects > Worksheets > Databases.
Select + to create a new SQL worksheet.

Copy and paste the usage example below onto your worksheet to call the stored procedure, replace with your values.

Check Input Parameters for more information.

/***************************************************
SmartMover USA - Verify Single Address Usage Example
***************************************************/
USE SMARTMOVER_USA.CORE;

/* Verify a single address, replace with your values*/
CALL VERIFY_SINGLE_ADDRESS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PAFID                  => '<YOUR_PAF_ID>'
   ,JOBID                  => '<YOUR_JOB_ID>'
   ,OPTIONS                => '<OptionName_1:Parameter,OptionName_2:Parameter>'
   ,COLUMNS                => '<Column_1,Column_2>'
   ,LISTNAME               => '<YOUR_LIST_NAME>'
   ,COMPANY                => 'Melissa'
   ,NAMEFULL               => ''
   ,ADDRESSLINE1           => '22382 Avenida Empresa'
   ,ADDRESSLINE2           => ''
   ,SUITE                  => ''
   ,PRIVATEMAILBOX         => ''
   ,URBANIZATION           => ''
   ,CITY                   => 'Rancho Santa Margarita'
   ,STATE                  => 'CA'
   ,POSTALCODE             => '92688'
   ,COUNTRY                => 'US'
   ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS    => '<Field_1,Field_2>'
   ,JOBS_TABLE_NAME        => '<JOBS_TABLE_NAME>'
);

/* Result view, if Output Table is provided */
SELECT *
FROM <OUTPUT_TABLE_NAME>;

Example of a stored procedure call from a SQL worksheet. Highlight the SQL statement then select Run button on the right top corner.

Input Parameters #

Input parameters when calling stored procedure VERIFY_SINGLE_ADDRESS.

Parameter	Data Type	Description	Example
LICENSE	VARCHAR	Required. The License Key with Credits issued by Melissa. Get it here.	REPLACE_WITH_YOUR_LICENSE_KEY
PAFID	VARCHAR	Required. Empty string is accepted. Only applies for Broker accounts and not to CCOA. For SmartMover Brokers with their own set of end users, use the PAF ID to identify which end user this record belongs to.
JOBID	VARCHAR	Required. This value will identify to the service which records belong to the same list for the purposes of the NCOA report. All records from the same list should have the same JobID. This is NOT a database unique key, please use the RecordID field for that.	jobid1234
OPTIONS	VARCHAR	Required. Empty string is accepted. [OptionName:Parameter], separate by comma(,). See more information about endpoint options.	ProcessingType:Standard
COLUMNS	VARCHAR	Required. Empty string is accepted. List of request columns, separate by comma(,). See more information about endpoint request columns.	MoveReturnCode,PrivateMailbox
LISTNAME	VARCHAR	Required. Empty string is accepted. Identifies the current list. It will be included in reports returned by SmartMover V3 after processing.	sm_snowflake
COMPANY	VARCHAR	Required. Empty string is accepted. The name of the business.	Melissa
ADDRESSLINE1	VARCHAR	Required. A minimum address location for the name/company contact.	22382 Avenida Empresa
ADDRESSLINE2	VARCHAR	Required. Empty string is accepted. The second address line.
NAMEFULL	VARCHAR	Required. Empty string is accepted. The full name of an individual.
SUITE	VARCHAR	Required. Empty string is accepted. The suite number.
PRIVATEMAILBOX	VARCHAR	Required. Empty string is accepted. The private mailbox number, if this address is a box in a private mailbox service.
URBANIZATION	VARCHAR	Required. Empty string is accepted. Only used for addresses in Puerto Rico. This is used to break ties between similar addresses in the same Postal Code.
CITY	VARCHAR	Required. The city name.	Rancho Santa Margarita
STATE	VARCHAR	Required. The state name.	CA
POSTALCODE	VARCHAR	Required. The five-digit ZIP Code, the first five digits of a ZIP+4, or a full nine-digit ZIP+4.	92688
COUNTRY	VARCHAR	Required. The country code. The Native App SmartMover USA: Snowflake can only update addresses within the United States.	US
OUTPUT_TABLE_NAME	VARCHAR	Required. Empty string is accepted. The output table name. A new table will be created in the same application database if it doesn’t exist. If the table exists, its structure must remain unchanged.	OUTPUT_TABLE_NAME
OUTPUT_TABLE_FIELDS	VARCHAR	Required. Case-sensitive. Empty string is accepted. Only valid when OUTPUT_TABLE_NAME provided. Fields in the output table; fields separated by comma(,) if not ‘All’. Field name must match in one or all in an example list. If omitted, additional requested COLUMNS will be added on top of Default Output Fields	RecordID, IN_PAFID, IN_JOBID, InComp, InNameFull, InAddressLine1, InAddressLine2, InSuite, InCity, InState, InPostal, INPMB, INURBAN, INCOUNTRY, Results, AddressExtras, AddressKey, AddressLine1, AddressLine2, AddressTypeCode, BaseMelissaAddressKey, CarrierRoute, City, CompanyName, CountryCode, DeliveryIndicator, DeliveryPointCheckDigit, DeliveryPointCode, MelissaAddressKey, MoveEffectiveDate, MoveTypeCode, MoveReturnCode, PostalCode, State, StateName, Urbanization, Response, Timestamp (GrpName) NameFirst, NameFull, NameLast, NameMiddle, NamePrefix, NameSuffix (GrpOriginal) OriginalAddressLine1, OriginalAddressLine2, OriginalCity, OriginalCityAbbreviation, OriginalCountryCode, OriginalPlus4, OriginalPostalCode, OriginalPrivateMailbox, OriginalResults, OriginalState, OriginalStateName, OriginalSuite, OriginalUrbanization (GrpParsed) AddressDeliveryInstallation, AddressHouseNumber, AddressLockBox, AddressPostDirection, AddressPreDirection, AddressPrivateMailboxName, AddressPrivateMailboxRange, AddressRouteService, AddressStreetName, AddressStreetSuffix, AddressSuiteName, AddressSuiteNumber (GrpStandardized) StandardizedAddressLine1, StandardizedAddressLine2, StandardizedBaseMelissaAddressKey, StandardizedCarrierRoute, StandardizedCity, StandardizedCityAbbreviation, StandardizedCountryCode, StandardizedDeliveryIndicator, StandardizedDeliveryPointCheckDigit, StandardizedDeliveryPointCode, StandardizedMelissaAddressKey, StandardizedPlus4, StandardizedPostalCode, StandardizedPrivateMailbox, StandardizedResults, StandardizedState, StandardizedStateName, StandardizedSuite, StandardizedUrbanization (Column Name Only) DPVFootNotes, MoveReturnCode, Plus4, PrivateMailbox, Suite
JOBS_TABLE_NAME	VARCHAR	Required. Empty string is accepted. Only valid when OUTPUT_TABLE_NAME provided. Automatically save summary report links to a report table. A new table will be created in the same application database if it doesn’t exist. See Save Summary Report Links for more information.	JOBS_TABLE_NAME

Output Examples #

Some common output examples are shown below.

Verify a single address and display the result in JSON format
Verify a single address into an output table with default output fields
Verify a single address with selected output fields
Verify a single address with ‘ALL’ output fields
Verify a single address with selected output fields, save summary report links

Verify a single address and display the result in JSON format

/***************************************************
SmartMover USA - Verify Single Address Usage Example
***************************************************/
USE SMARTMOVER_USA.CORE;

/* Verify a single address, replace with your values*/
CALL VERIFY_SINGLE_ADDRESS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PAFID                  => ''
   ,JOBID                  => '<YOUR_JOB_ID>'
   ,OPTIONS                => ''
   ,COLUMNS                => ''
   ,LISTNAME               => '<YOUR_LIST_NAME>'
   ,COMPANY                => 'Melissa'
   ,NAMEFULL               => ''
   ,ADDRESSLINE1           => '22382 Avenida Empresa'
   ,ADDRESSLINE2           => ''
   ,SUITE                  => ''
   ,PRIVATEMAILBOX         => ''
   ,URBANIZATION           => ''
   ,CITY                   => 'Rancho Santa Margarita'
   ,STATE                  => 'CA'
   ,POSTALCODE             => '92688'
   ,COUNTRY                => 'US'
   ,OUTPUT_TABLE_NAME      => ''
   ,OUTPUT_TABLE_FIELDS    => ''
   ,JOBS_TABLE_NAME        => ''
);

Output will be displayed in JSON format.

../../_images/Snowflake-SmartMover-USA-Stored-Procedures-Verify-Single-SQL-Result-JSON.png

Verify a single address into an output table with default output fields

If OUTPUT_TABLE_NAME is provided, a new table will be created if not already existed in the same application database with Default Output Fields.

Call stored procedure VERIFY_SINGLE_ADDRESS.

CALL VERIFY_SINGLE_ADDRESS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PAFID                  => ''
   ,JOBID                  => '<YOUR_JOB_ID>'
   ,OPTIONS                => ''
   ,COLUMNS                => ''
   ,LISTNAME               => '<YOUR_LIST_NAME>'
   ,COMPANY                => 'Melissa'
   ,NAMEFULL               => ''
   ,ADDRESSLINE1           => '22382 Avenida Empresa'
   ,ADDRESSLINE2           => ''
   ,SUITE                  => ''
   ,PRIVATEMAILBOX         => ''
   ,URBANIZATION           => ''
   ,CITY                   => 'Rancho Santa Margarita'
   ,STATE                  => 'CA'
   ,POSTALCODE             => '92688'
   ,COUNTRY                => 'US'
   ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS    => ''
   ,JOBS_TABLE_NAME        => ''
);

../../_images/Snowflake-SmartMover-USA-Stored-Procedures-Verify-Single-SQL-Result-Default.png

Query output table.
```
SELECT *
FROM <OUTPUT_TABLE_NAME>;
```

Verify a single address with selected output fields

Call stored procedure VERIFY_SINGLE_ADDRESS.

CALL VERIFY_SINGLE_ADDRESS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PAFID                  => ''
   ,JOBID                  => '<YOUR_JOB_ID>'
   ,OPTIONS                => ''
   ,COLUMNS                => ''
   ,LISTNAME               => '<YOUR_LIST_NAME>'
   ,COMPANY                => 'Melissa'
   ,NAMEFULL               => ''
   ,ADDRESSLINE1           => '22382 Avenida Empresa'
   ,ADDRESSLINE2           => ''
   ,SUITE                  => ''
   ,PRIVATEMAILBOX         => ''
   ,URBANIZATION           => ''
   ,CITY                   => 'Rancho Santa Margarita'
   ,STATE                  => 'CA'
   ,POSTALCODE             => '92688'
   ,COUNTRY                => 'US'
   ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS    => '<Field_1,Field_2>'
   ,JOBS_TABLE_NAME        => ''
);

Query output table.
```
SELECT *
FROM <OUTPUT_TABLE_NAME>;
```

Verify a single address with ‘ALL’ output fields

Call stored procedure VERIFY_SINGLE_ADDRESS.

CALL VERIFY_SINGLE_ADDRESS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PAFID                  => ''
   ,JOBID                  => '<YOUR_JOB_ID>'
   ,OPTIONS                => ''
   ,COLUMNS                => ''
   ,LISTNAME               => '<YOUR_LIST_NAME>'
   ,COMPANY                => 'Melissa'
   ,NAMEFULL               => ''
   ,ADDRESSLINE1           => '22382 Avenida Empresa'
   ,ADDRESSLINE2           => ''
   ,SUITE                  => ''
   ,PRIVATEMAILBOX         => ''
   ,URBANIZATION           => ''
   ,CITY                   => 'Rancho Santa Margarita'
   ,STATE                  => 'CA'
   ,POSTALCODE             => '92688'
   ,COUNTRY                => 'US'
   ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS    => 'All'
   ,JOBS_TABLE_NAME        => ''
);

Query output table.
```
SELECT *
FROM <OUTPUT_TABLE_NAME>;
```

Verify a single address with selected output fields, save summary report links

Call stored procedure VERIFY_SINGLE_ADDRESS.

CALL VERIFY_SINGLE_ADDRESS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PAFID                  => ''
   ,JOBID                  => '<YOUR_JOB_ID>'
   ,OPTIONS                => 'ProcessingType:Standard'
   ,COLUMNS                => 'GrpStandardized'
   ,LISTNAME               => '<YOUR_LIST_NAME>'
   ,COMPANY                => 'Melissa'
   ,NAMEFULL               => ''
   ,ADDRESSLINE1           => '22382 Avenida Empresa'
   ,ADDRESSLINE2           => ''
   ,SUITE                  => ''
   ,PRIVATEMAILBOX         => ''
   ,URBANIZATION           => ''
   ,CITY                   => 'Rancho Santa Margarita'
   ,STATE                  => 'CA'
   ,POSTALCODE             => '92688'
   ,COUNTRY                => 'US'
   ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS    => 'IN_JOBID,Results,MelissaAddressKey,StandardizedState'
   ,JOBS_TABLE_NAME        => '<JOBS_TABLE_NAME>'
);

Query output table.
```
SELECT *
FROM <OUTPUT_TABLE_NAME>;
```
See Save Summary Report Links for more details.

VERIFY_MULTIPLE_ADDRESSES #

The VERIFY_MULTIPLE_ADDRESSES procedure processes address records in batches, validating and standardizing key components using the SmartMover API.

It can handle tables of any size, returning the results in a specified output table and supports optional fields for flexible, accurate address validation.

Requirements #

To use this feature, you need to prepare an input table in Snowflake beforehand referencing our default input fields below.

Default Input Fields #

Column Name	Data Type	Description
RECORDID	NUMBER	Required. Record identifier, primary key.
COMPANY	VARCHAR	Required. Empty string is accepted. The name of the business.
NAMEFULL	VARCHAR	Required. Empty string is accepted. The full name of an individual.
ADDRESSLINE1	VARCHAR	Required. A minimum address location for the name/company contact.
ADDRESSLINE2	VARCHAR	Required. Empty string is accepted. The second address line.
SUITE	VARCHAR	Required. Empty string is accepted. The suite number.
PRIVATEMAILBOX	VARCHAR	Required. Empty string is accepted. The private mailbox number, if this address is a box in a private mailbox service.
URBANIZATION	VARCHAR	Required. Empty string is accepted. Only used for addresses in Puerto Rico. This is used to break ties between similar addresses in the same Postal Code.
CITY	VARCHAR	Required. The city name.
STATE	VARCHAR	Required. The state name.
POSTALCODE	VARCHAR	Required. The five-digit ZIP Code, the first five digits of a ZIP+4, or a full nine-digit ZIP+4.
COUNTRY	VARCHAR	Required. The country code. The Native App SmartMover USA: Snowflake can only update addresses within the United States.

SQL Script #

You can find the installed application database in Projects > Worksheets > Databases.

../../_images/Snowflake-SmartMover-USA-Stored-Procedures-Verify-Multiple-SQL-Sample-Structure.png

Setup and Sample Script #

The step-by-step example below shows how to use our stored procedure VERIFY_MULTIPLE_ADDRESSES with the default values. Replace with your values.

Step 0 - Prepare an input table.

Assume that the input table has the signature below:

CREATE TABLE IF NOT EXISTS <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> (
   RECID NUMBER(38,0) NOT NULL
  ,Company VARCHAR(16777216) DEFAULT ('')
  ,FullName VARCHAR(16777216) DEFAULT ('')
  ,AddressLine1 VARCHAR(16777216) DEFAULT ('')
  ,AddressLine2 VARCHAR(16777216) DEFAULT ('')
  ,Suite VARCHAR(16777216) DEFAULT ('')
  ,PMB VARCHAR(16777216) DEFAULT ('')
  ,URB VARCHAR(16777216) DEFAULT ('')
  ,City VARCHAR(16777216) DEFAULT ('')
  ,State VARCHAR(16777216) DEFAULT ('')
  ,PostalCode VARCHAR(16777216) DEFAULT ('')
  ,Country VARCHAR(16777216) DEFAULT ('')
  ,PRIMARY KEY (RECID)
);

This input table has different column names from the Default Input Fields. Mapping column names in Step 2 is necessary for the program to get the correct parameters.

Step 1 - Grant Required Privileges.

Ensure the application has the necessary access to the input table. Replace placeholders with your actual values.

/********************************************************
SmartMover USA - Verify Multiple Addresses Usage Example
********************************************************/
/* Grant usage on input table to the application, replace with your values.*/
GRANT USAGE ON DATABASE <INPUT_DATABASE> TO APPLICATION SMARTMOVER_USA;
GRANT USAGE ON SCHEMA <INPUT_DATABASE>.<INPUT_SCHEMA> TO APPLICATION SMARTMOVER_USA;
GRANT SELECT ON TABLE <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> TO APPLICATION SMARTMOVER_USA;

Step 2 - Map Input Columns with the Default Input Fields.

Set the required parameters and map your input columns to our Default Input Fields.

You can skip the mapping step if your input table matches our Default Input Fields exactly.

 USE SMARTMOVER_USA.CORE;

 /* Set input parameters, replace with your values */
 SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
 SET PAFID               = '<YOUR_PAF_ID>';
 SET JOBID               = '<YOUR_JOB_ID>';
 SET OPTIONS             = '<OptionName_1:Parameter,OptionName_2:Parameter>';
 SET COLUMNS             = '<Column_1,Column_2>';
 SET LISTNAME            = '<YOUR_LIST_NAME>';
 SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
 SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
 SET OUTPUT_TABLE_FIELDS = '<Field_1,Field_2>';
 SET JOBS_TABLE_NAME     = '<JOBS_TABLE_NAME>';

 /* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
 CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
 SELECT
     RECID                       AS RECORDID
    ,COALESCE(COMPANY, '')       AS COMPANY
    ,COALESCE(FULLNAME, '')      AS NAMEFULL
    ,COALESCE(ADDRESSLINE1, '')  AS ADDRESSLINE1
    ,COALESCE(ADDRESSLINE2, '')  AS ADDRESSLINE2
    ,COALESCE(SUITE, '')         AS SUITE
    ,COALESCE(PMB, '')           AS PRIVATEMAILBOX
    ,COALESCE(URB, '')           AS URBANIZATION
    ,COALESCE(CITY, '')          AS CITY
    ,COALESCE(STATE, '')         AS STATE
    ,COALESCE(POSTALCODE, '')    AS POSTALCODE
    ,COALESCE(COUNTRY, '')       AS COUNTRY
 FROM IDENTIFIER($INPUT_TABLE_NAME);

Step 3 - Call stored procedure VERIFY_MULTIPLE_ADDRESSES() to verify your data.

Check Input Parameters for more information.

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
    LICENSE                => $LICENSE
   ,PAFID                  => $PAFID
   ,JOBID                  => $JOBID
   ,OPTIONS                => $OPTIONS
   ,COLUMNS                => $COLUMNS
   ,LISTNAME               => $LISTNAME
   ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
   ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
   ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
   ,JOBS_TABLE_NAME        => $JOBS_TABLE_NAME
   -- Size limit for variables is 256. If your input string exceeds the limit, parse the string directly in the call
   -- e.g. OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
);

/* Output table view */
SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);

Full SQL Script

/********************************************************
SmartMover USA - Verify Multiple Addresses Usage Example
********************************************************/
/* Grant usage on input table to the application, replace with your values.*/
GRANT USAGE ON DATABASE <INPUT_DATABASE> TO APPLICATION SMARTMOVER_USA;
GRANT USAGE ON SCHEMA <INPUT_DATABASE>.<INPUT_SCHEMA> TO APPLICATION SMARTMOVER_USA;
GRANT SELECT ON TABLE <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> TO APPLICATION SMARTMOVER_USA;

USE SMARTMOVER_USA.CORE;

/* Set input parameters, replace with your values */
SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET PAFID               = '<YOUR_PAF_ID>';
SET JOBID               = '<YOUR_JOB_ID>';
SET OPTIONS             = '<OptionName_1:Parameter,OptionName_2:Parameter>';
SET COLUMNS             = '<Column_1,Column_2>';
SET LISTNAME            = '<YOUR_LIST_NAME>';
SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
SET OUTPUT_TABLE_FIELDS = '<Field_1,Field_2>';
SET JOBS_TABLE_NAME     = '<JOBS_TABLE_NAME>';

/* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
    RECID                       AS RECORDID
   ,COALESCE(COMPANY, '')       AS COMPANY
   ,COALESCE(FULLNAME, '')      AS NAMEFULL
   ,COALESCE(ADDRESSLINE1, '')  AS ADDRESSLINE1
   ,COALESCE(ADDRESSLINE2, '')  AS ADDRESSLINE2
   ,COALESCE(SUITE, '')         AS SUITE
   ,COALESCE(PMB, '')           AS PRIVATEMAILBOX
   ,COALESCE(URB, '')           AS URBANIZATION
   ,COALESCE(CITY, '')          AS CITY
   ,COALESCE(STATE, '')         AS STATE
   ,COALESCE(POSTALCODE, '')    AS POSTALCODE
   ,COALESCE(COUNTRY, '')       AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
    LICENSE                => $LICENSE
   ,PAFID                  => $PAFID
   ,JOBID                  => $JOBID
   ,OPTIONS                => $OPTIONS
   ,COLUMNS                => $COLUMNS
   ,LISTNAME               => $LISTNAME
   ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
   ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
   ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
   ,JOBS_TABLE_NAME        => $JOBS_TABLE_NAME
   -- Size limit for variables is 256. If your input string exceeds the limit, parse the string directly in the call
   -- e.g. OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
);

/* Output table view */
SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);

Input Parameters #

Input parameters when calling stored procedure VERIFY_MULTIPLE_ADDRESSES.

Parameter	Data Type	Description	Example
LICENSE	VARCHAR	Required. The License Key with Credits issued by Melissa. Get it here.	REPLACE_WITH_YOUR_LICENSE_KEY
PAFID	VARCHAR	Required. Empty string is accepted. Only applies for Broker accounts and not to CCOA. For SmartMover Brokers with their own set of end users, use the PAF ID to identify which end user this record belongs to.
JOBID	VARCHAR	Required. This value will identify to the service which records belong to the same list for the purposes of the NCOA report. All records from the same list should have the same JobID. This is NOT a database unique key, please use the RecordID field for that.	jobid1234
OPTIONS	VARCHAR	Required. Empty string is accepted. [OptionName:Parameter], separate by comma(,). See more information about endpoint options.	ProcessingType:Standard
COLUMNS	VARCHAR	Required. Empty string is accepted. List of request columns, separate by comma(,). See more information about endpoint request columns.	MoveReturnCode,PrivateMailbox
INPUT_TABLE_NAME	VARCHAR	Required. The name of the input table.	IN_DB.IN_SCHEMA.INPUT_TABLE_NAME
OUTPUT_TABLE_NAME	VARCHAR	Required. The output table name. A new table will be created in the same application database if it doesn’t exist. If the table exists, its structure must remain unchanged.	OUTPUT_TABLE_NAME
OUTPUT_TABLE_FIELDS	VARCHAR	Required. Case-sensitive. Empty string is accepted. Fields in the output table; fields separated by comma(,) if not ‘All’. Field name must match in one or all in an example list. If omitted, additional requested COLUMNS will be added on top of Default Output Fields.	RecordID, IN_PAFID, IN_JOBID, InComp, InNameFull, InAddressLine1, InAddressLine2, InSuite, InCity, InState, InPostal, INPMB, INURBAN, INCOUNTRY, Results, AddressExtras, AddressKey, AddressLine1, AddressLine2, AddressTypeCode, BaseMelissaAddressKey, CarrierRoute, City, CompanyName, CountryCode, DeliveryIndicator, DeliveryPointCheckDigit, DeliveryPointCode, MelissaAddressKey, MoveEffectiveDate, MoveTypeCode, MoveReturnCode, PostalCode, State, StateName, Urbanization, Response, Timestamp (GrpName) NameFirst, NameFull, NameLast, NameMiddle, NamePrefix, NameSuffix (GrpOriginal) OriginalAddressLine1, OriginalAddressLine2, OriginalCity, OriginalCityAbbreviation, OriginalCountryCode, OriginalPlus4, OriginalPostalCode, OriginalPrivateMailbox, OriginalResults, OriginalState, OriginalStateName, OriginalSuite, OriginalUrbanization (GrpParsed) AddressDeliveryInstallation, AddressHouseNumber, AddressLockBox, AddressPostDirection, AddressPreDirection, AddressPrivateMailboxName, AddressPrivateMailboxRange, AddressRouteService, AddressStreetName, AddressStreetSuffix, AddressSuiteName, AddressSuiteNumber (GrpStandardized) StandardizedAddressLine1, StandardizedAddressLine2, StandardizedBaseMelissaAddressKey, StandardizedCarrierRoute, StandardizedCity, StandardizedCityAbbreviation, StandardizedCountryCode, StandardizedDeliveryIndicator, StandardizedDeliveryPointCheckDigit, StandardizedDeliveryPointCode, StandardizedMelissaAddressKey, StandardizedPlus4, StandardizedPostalCode, StandardizedPrivateMailbox, StandardizedResults, StandardizedState, StandardizedStateName, StandardizedSuite, StandardizedUrbanization (Column Name Only) DPVFootNotes, MoveReturnCode, Plus4, PrivateMailbox, Suite
JOBS_TABLE_NAME	VARCHAR	Required. Empty string is accepted. Automatically save summary report links to a report table. A new table will be created in the same application database if it doesn’t exist. See Save Summary Report Links for more information.	JOBS_TABLE_NAME

Output Examples #

Some common output examples are shown below.

Verify multiple addresses with the default output fields
Verify multiple addresses with selected output fields
Verify multiple addresses with ‘ALL’ output fields
Verify multiple addresses with selected output fields and save summary report links

Verify multiple addresses with the default output fields

Call the stored procedure to verify multiple addresses with Default Output Fields.

USE SMARTMOVER_USA.CORE;

/* Set input parameters, replace with your values */
 SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
 SET PAFID               = '';
 SET JOBID               = '<YOUR_JOB_ID>';
 SET OPTIONS             = ''
 SET LISTNAME            = '<YOUR_LIST_NAME>';
 SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
 SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
 SET OUTPUT_TABLE_FIELDS = '';
 SET JOBS_TABLE_NAME     = '';

/* Map your input columns with our default schema, replace with your column names */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
     RECID                       AS RECORDID
    ,COALESCE(COMPANY, '')       AS COMPANY
    ,COALESCE(FULLNAME, '')      AS NAMEFULL
    ,COALESCE(ADDRESSLINE1, '')  AS ADDRESSLINE1
    ,COALESCE(ADDRESSLINE2, '')  AS ADDRESSLINE2
    ,COALESCE(SUITE, '')         AS SUITE
    ,COALESCE(PMB, '')           AS PRIVATEMAILBOX
    ,COALESCE(URB, '')           AS URBANIZATION
    ,COALESCE(CITY, '')          AS CITY
    ,COALESCE(STATE, '')         AS STATE
    ,COALESCE(POSTALCODE, '')    AS POSTALCODE
    ,COALESCE(COUNTRY, '')       AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
    LICENSE                => $LICENSE
   ,PAFID                  => $PAFID
   ,JOBID                  => $JOBID
   ,OPTIONS                => $OPTIONS
   ,LISTNAME               => $LISTNAME
   ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
   ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
   ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
   ,JOBS_TABLE_NAME        => $JOBS_TABLE_NAME
);

Query output table.

SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);

Verify multiple addresses with selected output fields

Call stored procedure to verify multiple addresses from an input table with selected output fields.

USE SMARTMOVER_USA.CORE;

/* Set input parameters, replace with your values */
SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET PAFID               = '';
SET JOBID               = '<YOUR_JOB_ID>';
SET OPTIONS             = ''
SET LISTNAME            = '<YOUR_LIST_NAME>';
SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
SET OUTPUT_TABLE_FIELDS = '<Field_1,Field_2>';
SET JOBS_TABLE_NAME     = '';

/* Map your input columns with our default schema, replace with your column names */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
     RECID                       AS RECORDID
    ,COALESCE(COMPANY, '')       AS COMPANY
    ,COALESCE(FULLNAME, '')      AS NAMEFULL
    ,COALESCE(ADDRESSLINE1, '')  AS ADDRESSLINE1
    ,COALESCE(ADDRESSLINE2, '')  AS ADDRESSLINE2
    ,COALESCE(SUITE, '')         AS SUITE
    ,COALESCE(PMB, '')           AS PRIVATEMAILBOX
    ,COALESCE(URB, '')           AS URBANIZATION
    ,COALESCE(CITY, '')          AS CITY
    ,COALESCE(STATE, '')         AS STATE
    ,COALESCE(POSTALCODE, '')    AS POSTALCODE
    ,COALESCE(COUNTRY, '')       AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
    LICENSE                => $LICENSE
   ,PAFID                  => $PAFID
   ,JOBID                  => $JOBID
   ,OPTIONS                => $OPTIONS
   ,LISTNAME               => $LISTNAME
   ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
   ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
   ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
   ,JOBS_TABLE_NAME        => $JOBS_TABLE_NAME
   -- Size limit for variables is 256. If your input string exceeds the limit, parse the string directly in the call
   -- e.g. OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
);

Query output table.

SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);

Verify multiple addresses with ‘ALL’ output fields

Call stored procedure to verify multiple addresses from an input table with ‘All’ output fields.

USE SMARTMOVER_USA.CORE;

/* Set input parameters, replace with your values */
SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET PAFID               = '';
SET JOBID               = '<YOUR_JOB_ID>';
SET OPTIONS             = ''
SET LISTNAME            = '<YOUR_LIST_NAME>';
SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
SET OUTPUT_TABLE_FIELDS = 'All';
SET JOBS_TABLE_NAME     = '';

/* Map your input columns with our default schema, replace with your column names */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
     RECID                       AS RECORDID
    ,COALESCE(COMPANY, '')       AS COMPANY
    ,COALESCE(FULLNAME, '')      AS NAMEFULL
    ,COALESCE(ADDRESSLINE1, '')  AS ADDRESSLINE1
    ,COALESCE(ADDRESSLINE2, '')  AS ADDRESSLINE2
    ,COALESCE(SUITE, '')         AS SUITE
    ,COALESCE(PMB, '')           AS PRIVATEMAILBOX
    ,COALESCE(URB, '')           AS URBANIZATION
    ,COALESCE(CITY, '')          AS CITY
    ,COALESCE(STATE, '')         AS STATE
    ,COALESCE(POSTALCODE, '')    AS POSTALCODE
    ,COALESCE(COUNTRY, '')       AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
    LICENSE                => $LICENSE
   ,PAFID                  => $PAFID
   ,JOBID                  => $JOBID
   ,OPTIONS                => $OPTIONS
   ,LISTNAME               => $LISTNAME
   ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
   ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
   ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
   ,JOBS_TABLE_NAME        => $JOBS_TABLE_NAME
);

Query output table.

SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);

Verify multiple addresses with selected output fields and save summary report links

Call stored procedure to verify multiple addresses from an input table.

USE SMARTMOVER_USA.CORE;

/* Set input parameters, replace with your values */
SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET PAFID               = '';
SET JOBID               = '<YOUR_JOB_ID>';
SET OPTIONS             = 'GrpStandardized'
SET LISTNAME            = '<YOUR_LIST_NAME>';
SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
SET OUTPUT_TABLE_FIELDS = 'RecordID,Results,MelissaAddressKey,StandardizedState';
SET JOBS_TABLE_NAME     = '<JOBS_TABLE_NAME>';

/* Map your input columns with our default schema, replace with your column names */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
     RECID                       AS RECORDID
    ,COALESCE(COMPANY, '')       AS COMPANY
    ,COALESCE(FULLNAME, '')      AS NAMEFULL
    ,COALESCE(ADDRESSLINE1, '')  AS ADDRESSLINE1
    ,COALESCE(ADDRESSLINE2, '')  AS ADDRESSLINE2
    ,COALESCE(SUITE, '')         AS SUITE
    ,COALESCE(PMB, '')           AS PRIVATEMAILBOX
    ,COALESCE(URB, '')           AS URBANIZATION
    ,COALESCE(CITY, '')          AS CITY
    ,COALESCE(STATE, '')         AS STATE
    ,COALESCE(POSTALCODE, '')    AS POSTALCODE
    ,COALESCE(COUNTRY, '')       AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
    LICENSE                => $LICENSE
   ,PAFID                  => $PAFID
   ,JOBID                  => $JOBID
   ,OPTIONS                => $OPTIONS
   ,LISTNAME               => $LISTNAME
   ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
   ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
   ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
   ,JOBS_TABLE_NAME        => $JOBS_TABLE_NAME
   -- Size limit for variables is 256. If your input string exceeds the limit, parse the string directly in the call
   -- e.g. OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
);

Query output table.

SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);

See Save Summary Report Links for more details.

DROP_OUTPUT_TABLE #

After the creation, output table is only allowed for:

Select.

SELECT TOP 100 *
FROM <OUTPUT_TABLE_NAME>
ORDER BY (RECORDID);

Insert.

By calling the same stored procedure without changing the input structure, new records will be inserted to the same table.
Drop.

Use stored procedure DROP_OUTPUT_TABLE if you wish to remove the table from the application database. Only tables created by the procedure call can be dropped.
```
CALL SMARTMOVER_USA.CORE.DROP_OUTPUT_TABLE('<OUTPUT_TABLE_NAME>');
```

Default Output Fields #

If OUTPUT_TABLE_FIELDS is an empty string, output table will have a default schema as below.

Column Name	Data Type
RECORDID	NUMBER
IN_PAFID	VARCHAR
IN_JOBID	VARCHAR
INADDRESSLINE1	VARCHAR
INCITY	VARCHAR
INSTATE	VARCHAR
INPOSTAL	VARCHAR
MELISSAADDRESSKEY	VARCHAR
BASEMELISSAADDRESSKEY	VARCHAR
RESULTS	VARCHAR
ADDRESSLINE1	VARCHAR
ADDRESSTYPECODE	VARCHAR
CARRIERROUTE	VARCHAR
CITY	VARCHAR
CITYABBREVIATION	VARCHAR
COMPANYNAME	VARCHAR
DELIVERYINDICATOR	VARCHAR
MOVEEFFECTIVEDATE	VARCHAR
MOVETYPECODE	VARCHAR
POSTALCODE	VARCHAR
STATE	VARCHAR
URBANIZATION	VARCHAR
ADDRESSEXTRAS	VARCHAR
TIMESTAMP	TIMESTAMP_NTZ

Save Summary Report Links #

You can save the summary report links to an output table by providing JOBS_TABLE_NAME in the stored procedure call.

A new jobs table will be created in the same application database with the default schema as below if it doesn’t exist.

Column Name	Data Type
JOBID	VARCHAR
PAFID	VARCHAR
LISTNAME	VARCHAR
CASS_REPORT_LINK	VARCHAR
NCOA_REPORT_LINK	VARCHAR
TIMESTAMP	VARCHAR

Query the jobs table to get the summary report links.

SELECT *
FROM <JOBS_TABLE_NAME>
WHERE JOBID = '<YOUR_JOB_ID>';

Versions and Updates #

Check for current version #

Select Data Products > Apps, the current version of the app will be on display.
Or, run this query in Projects > Worksheets.
```
SHOW APPLICATIONS;
```

Updates #

When Melissa releases a new version of the Native App, your installed application will get updated automatically.

However, you will need to reinstall the app’s functionality after the upgrade is complete, as described in Application Configuration.

Result Codes #

For the full list of result codes returned by Native App SmartMover USA: Snowflake, please visit here.

Interpreting Results #

Melissa products use a result code system to indicate data quality; the status and any errors. These result codes are four-character codes (two letters followed by two numbers), delimited by commas. Result code definitions are shared among Melissa products. Instead of looking at multiple properties and methods to determine status, you can look at the output of the results parameter.

Examples

Code	Description	Recommendation
`CS01`	The record is a ‘move’ and a new ‘moved to’ address was provided.	Good
`CS02`	The record is not a ‘move’ but was standardized.	Good
`CS03`	The record is matched to change of address file but did not satisfy all requirements to produce a ‘moved to’ address.	Medium
`CS04`	The record is a ‘move’ but could not provide a ‘moved to’ address.	Good
`CS10`	The record is classified as an individual.	Good
`CS11`	The record is classified as a family.	Good
`CM01`	A COA was found for an Individual, Business, or Family.	Good
`CM03`	A COA was found. The customer moved and left no forwarding address (MLNA).	Medium
`ASO1`	The address is valid and deliverable according to official postal agencies.	Good
`AE**`	Associated Error with input address	Bad
`AC**`	A change was made to an input address property	Medium

Reference Guide

Contents

Reference Guide#

2-Step Configuration #

Events and Logs #

Stored Procedures #

VERIFY_SINGLE_ADDRESS #

Verification Form #

Input Example #

Output Examples #

SQL Script #

Setup and Sample Script #

Input Parameters #

Output Examples #

VERIFY_MULTIPLE_ADDRESSES #

Requirements #

Default Input Fields #

SQL Script #

Setup and Sample Script #

Input Parameters #

Output Examples #

DROP_OUTPUT_TABLE #

Default Output Fields #

Save Summary Report Links #

Versions and Updates #

Check for current version #

Updates #

Result Codes #

Interpreting Results #