Reference Guide#

Attention

🚧🛠️ Pardon Our Dust! 🛠️🚧

We’re in the process of enhancing our website to provide you with a better experience. While we work diligently, you might notice a few things out of place. Please excuse the temporary mess as we strive to improve our site.

Please contact us at MelissaDocs@Melissa.com if you have any questions.

2-Step Configuration #

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

Events and Logs #

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.

There are 2 ways to verify an address in Native App SmartMover 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 > Start Verifying.

Input Example #

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

Output Examples #

Verify a single address #

Result will be displayed in JSON format.

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

Verify a single address and insert the result to an output table #

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

../../_images/Snowflake-SmartMover-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-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-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 - Verify Single Address Usage Example
******************************************************************/
USE SMARTMOVER.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>'
   ,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>'
);

/* 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.

../../_images/Snowflake-SmartMover-Stored-Procedures-Verify-Single-SQL-Sample.png

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 and CCOA 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 colon(,) See more information about OPTIONS	NumberofMonthsRequested:48
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: 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_FIELDS	VARCHAR	Required. Case-sensitive. Empty string is accepted. Only valid when OUTPUT_TABLE_NAME provided. Fields in the output table; fields separates by comma(,) if not ‘All’. Field name must match in one or all in an example list. If omitted, see Default Ouput Fields.	RecordID, 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

Output Examples #

Verify a single address #

/******************************************************************
SmartMover - Verify Single Address Usage Example
******************************************************************/
USE SMARTMOVER.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>'
   ,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    => ''
);

Output will be displayed in JSON format.

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

Verify a single address and insert the result into an output table #

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

Call stored procedure VERIFY_SINGLE_ADDRESS.

 /* 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>'
    ,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    => ''
 );

../../_images/Snowflake-SmartMover-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                  => '<YOUR_PAF_ID>'
    ,JOBID                  => '<YOUR_JOB_ID>'
    ,OPTIONS                => '<OptionName_1:Parameter,OptionName_2:Parameter>'
    ,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>'
);

../../_images/Snowflake-SmartMover-Stored-Procedures-Verify-Single-SQL-Result-Selected-Fields.png

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

Verify a single address with ‘ALL’ output table fields #

Call stored procedure VERIFY_SINGLE_ADDRESS.

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

../../_images/Snowflake-SmartMover-Stored-Procedures-Verify-Single-SQL-Result-All-Fields.png

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

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: 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-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 INT IDENTITY
   ,Company VARCHAR DEFAULT ('')
   ,FullName VARCHAR DEFAULT ('')
   ,AddressLine1 VARCHAR DEFAULT ('')
   ,AddressLine2 VARCHAR DEFAULT ('')
   ,Suite VARCHAR DEFAULT ('')
   ,PMB VARCHAR DEFAULT ('')
   ,URB VARCHAR DEFAULT ('')
   ,City VARCHAR DEFAULT ('')
   ,State VARCHAR DEFAULT ('')
   ,PostalCode VARCHAR DEFAULT ('')
   ,Country VARCHAR DEFAULT ('')
) CLUSTER BY (RecId);

This input table has different column names from the Default Input Fields. Mapping column names in Step 2 is neccessary 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 - Veirfy 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;
GRANT USAGE ON SCHEMA <INPUT_DATABASE>.<INPUT_SCHEMA> TO APPLICATION SMARTMOVER;
GRANT SELECT ON TABLE <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> TO APPLICATION SMARTMOVER;

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.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 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>';

 /* 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 Parameter for more information.

/* 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
   -- 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 - 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;
GRANT USAGE ON SCHEMA <INPUT_DATABASE>.<INPUT_SCHEMA> TO APPLICATION SMARTMOVER;
GRANT SELECT ON TABLE <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> TO APPLICATION SMARTMOVER;

USE SMARTMOVER.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 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>';

/* 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
   ,LISTNAME               => $LISTNAME
   ,INPUT_TABLE_NAME       => TABLE(INPUT_RECORDS)
   ,OUTPUT_TABLE_NAME      => $OUTPUT_TABLE_NAME
   ,OUTPUT_TABLE_FIELDS    => $OUTPUT_TABLE_FIELDS
   -- 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 Parameter #

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
OPTIONS	VARCHAR	Required. Empty string is accepted [OptionName:Parameter], separate by colon(,) See more info about OPTIONS.	NumberofMonthsRequested:48
INPUT_TABLE_NAME	VARCHAR	Required. Name of the input table
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_FIELDS	VARCHAR	Required. Case-sensitive. Empty string is accepted. Fields in the output table; fields separates by comma(,) if not ‘All’. Field name must match in one or all in an example list. If omitted, see Default Ouput Fields.	RecordID, 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

Output Examples #

Verify a table of addresses with the default output fields #

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

USE SMARTMOVER.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 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 = '';

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

../../_images/Snowflake-SmartMover-Stored-Procedures-Verify-Multiple-Default-Output.png

Query output table.

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

../../_images/Snowflake-SmartMover-Stored-Procedures-Verify-Multiple-Default-Output-Table-View.png

Verify a table of addresses with selected output fields #

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

USE SMARTMOVER.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 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>';

/* 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
   -- 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'
);

../../_images/Snowflake-SmartMover-Stored-Procedures-Verify-Multiple-Selected-Output.png

Query output table.

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

../../_images/Snowflake-SmartMover-Stored-Procedures-Verify-Multiple-Selected-Output-Table-View.png

Verify a table of addresses with ‘ALL’ output fields #

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

USE SMARTMOVER.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 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';

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

../../_images/Snowflake-SmartMover-Stored-Procedures-Verify-Multiple-All-Output.png

Query output table.

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

../../_images/Snowflake-SmartMover-Stored-Procedures-Verify-Multiple-All-Output-Table-View.png

GET_NCOA_SUMMARY_REPORT_LINKS #

The GET_NCOA_SUMMARY_REPORT_LINKS procedure processes inputting license, PAFID and JobID to return NCOA summary report links via the SmartMover Web Service API.

There are 2 ways to get NCOA summary report links in Native App SmartMover for Snowflake:

Fill in the Get NCOA Summary Report Links Form on the Native App interface.
Manually run a Snowflake SQL script to call the stored procedure.

Get NCOA Summary Report Links Form #

In your Snowflake account, select Data Products > Apps > SMARTMOVER > Menu > Get NCOA Summary Report Links > Try It Now > Start Getting NCOA Report.

Input Example #

Enter the License Key and the Job ID you want to get the NCOA Summary Report Links for.
Select Get NCOA Report.
Result will be displayed on the left sidebar.

Output Examples #

Output is displayed in JSON format.

../../_images/Snowflake-SmartMover-Stored-Procedures-Get-NCOA-Report-Links-Try-Now-Output.png

SQL Script #

Manually call our procedures using a Snowflake SQL 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 - Get NCOA Summary Report Links Usage Example
******************************************************************/
USE SMARTMOVER.CORE;

/* Get NCOA report links for a JobId, replace with your values*/
CALL GET_NCOA_SUMMARY_REPORT_LINKS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,PAFID                  => '<YOUR_PAF_ID>'
   ,JOBID                  => '<YOUR_JOB_ID>'
);

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

../../_images/Snowflake-SmartMover-Stored-Procedures-Get-NCOA-Report-Links-SQL-Sample.png

Input Parameters #

Input parameters when calling stored procedure GET_NCOA_SUMMARY_REPORT_LINKS.

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 and CCOA 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.

testsnowflake

Output Examples #

Output is returned in JSON format.

../../_images/Snowflake-SmartMover-Stored-Procedures-Get-NCOA-Report-Links-SQL-Sample-Output.png

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.CORE.DROP_OUTPUT_TABLE('<OUTPUT_TABLE_NAME>');
```

Default Ouput Fields #

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

Column Name	Data Type
RECORDID	NUMBER
INCOMP	VARCHAR
INNAMEFULL	VARCHAR
INADDRESSLINE1	VARCHAR
INSUITE	VARCHAR
INCITY	VARCHAR
INSTATE	VARCHAR
INPOSTAL	VARCHAR
RESULTS	VARCHAR
ADDRESSLINE1	VARCHAR
CITY	VARCHAR
STATE	VARCHAR
POSTALCODE	VARCHAR
PRIVATEMAILBOX	VARCHAR
URBANIZATION	VARCHAR
COMPANYNAME	VARCHAR
MOVEEFFECTIVEDATE	VARCHAR
MOVETYPECODE	VARCHAR
MOVERETURNCODE	VARCHAR
TIMESTAMP	TIMESTAMP_NTZ

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 #

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.

Code	Description	Recommendation
CS01	Move with New Address	Good
CS02	Standardized Address	Good
CS03	Move Input Requirements not Satisfied	Medium
CS04	Move but No New Address	Good
CS10	Individual Move	Good
CS11	Family Move	Good
CM01	COA Match	Good
CM03	Moved no forwarding	Medium
ASO1	Address was verified	Good
AE**	Associated Error with input address	Bad
AC**	A change was made to an input address property	Medium

For the full list of all possible result codes, please visit here.

Reference Guide

Contents

Reference Guide#