Reference Guide#

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 components using the Global Address Verification API. It returns the verification results in JSON format or optional output table, supporting optional fields for flexible and accurate address validation.

To get started, please contact a sales representative at Snowflakemarketplace@Melissa.com for your License Key.

There are 2 ways to verify an address in Native App Global Address Verification for Snowflake:

  1. Fill in the Verification Form on the Native App interface

  2. Manually run a Snowflake SQL script to call the stored procedure

Verification Form#

In your Snowflake account, select Data Products > Apps > GLOBAL_ADDRESS_VERIFICATION > Menu > Verify Single Address > Try It Now > Start Verifying

Input Example#

  1. Enter the License Key and address you want to verify

  2. Select Verify

  3. Result will be displayed on the left sidebar

Output Examples#
Verify a single address#

Result will be displayed in JSON format.

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

Verify a single address with selected output table fields#

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

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

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

SQL Script#

Manually call our procedures using a Snowflake SQL script.

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

Setup and Sample Script#

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

  2. Select + to create a new SQL worksheet

  3. 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.

    /******************************************************************
Global Address Verification - Single Address Usage Example
******************************************************************/
USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Verify a single address, replace with your values*/
CALL VERIFY_SINGLE_ADDRESS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,ADDRESS_LINE1          => '22382 Avenida Empresa'
   ,LOCALITY               => 'Rancho Santa Margarita'
   ,ADMINISTRATIVE_AREA    => 'CA'
   ,POSTAL_CODE            => '92688'
   ,COUNTRY                => 'US'
   ,OUTPUT_TABLE_NAME      => '<YOUR_OUTPUT_TABLE_NAME>'
   ,OUTPUT_TABLE_FIELDS    => 'Results,FormattedAddress'
);

/* Result view, if Output Table is provided */
SELECT *
FROM <YOUR_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#

Parameter

Data Type

Description

Example

LICENSE

VARCHAR

Required.
The License Key with Credits issued by Melissa.

Get it here.

MY_LICENSE_KEY

OPTIONS

VARCHAR

Optional.
[OptionName:Parameter], separate by colon(,)

See more information about OPTIONS

USPreferredCityNames:on, USStandardizationType:short

ORGANIZATION_NAME

VARCHAR

Optional.
The organization name associated with the address record.

LAST_NAME

VARCHAR

Optional. US Only.
For addresses missing a secondary like a suite or apartment, we can use the last name and append that information for residential addresses.

ADDRESS_LINE1

VARCHAR

Required.
The input field for the address

This should contain the delivery address information (house number, thoroughfare, building, suite, etc.) but should not contain locality information (locality, administrative area, postal code, etc.) which have their own inputs.

22382 Avenida Empresa

ADDRESS_LINE2

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE3

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE4

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE5

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE6

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE7

VARCHAR

Optional.
The input field for the address.

ADDRESS_LINE8

VARCHAR

Optional.
The input field for the address.

DOUBLE_DEPENDENT_LOCALITY

VARCHAR

Optional.
The smallest population center data element.

This depends on the Locality and DependentLocality elements.

DEPENDENT_LOCALITY

VARCHAR

Optional.
Urbanization.

The smaller population center data element. This depends on the Locality element. In terms of US Addresses, this element applies only to Puerto Rican addresses. It is used to break ties when a ZIP Code™ is linked to multiple instances of the same address.

LOCALITY

VARCHAR

Required.
City, Municipality.

The most common population center data element.

Rancho Santa Margarita

SUB_ADMINISTRATIVE_AREA

VARCHAR

Optional.
County.

The smallest geographic data element.

ADMINISTRATIVE_AREA

VARCHAR

Required.
State, Province.

The most common geographic data element.

CA

POSTAL_CODE

VARCHAR

Required.
ZIP, Postcode.

The complete postal code for a particular delivery point. If all three elements are provided and the PostalCode is incorrect, it can be corrected from the data on the Locality and AdministrativeArea.

92688

SUB_NATIONAL_AREA

VARCHAR

Optional.
The administrative region within a country on an arbitrary level below that of the sovereign state.

COUNTRY

VARCHAR

Required.
The country name, abbreviation, or code.

OUTPUT_TABLE_NAME

VARCHAR

Optional.
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

Optional. Case-sensitive.

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.

Results,
FormattedAddress,
Organization,
AddressLine1,
AddressLine2,
AddressLine3,
AddressLine4,
AddressLine5,
AddressLine6,
AddressLine7,
AddressLine8,
SubPremises,
DoubleDependentLocality,
DependentLocality,
Locality,
SubAdministrativeArea,
AdministrativeArea,
PostalCode,
PostalCodeType,
AddressType,
AddressKey,
SubNationalArea,
CountryName,
CountryISO3166_1_Alpha2,
CountryISO3166_1_Alpha3,
CountryISO3166_1_Numeric,
CountrySubdivisionCode,
Thoroughfare,
ThoroughfarePreDirection,
ThoroughfareLeadingType,
ThoroughfareName,
ThoroughfareTrailingType,
ThoroughfarePostDirection,
DependentThoroughfare,
DependentThoroughfarePreDirection,
DependentThoroughfareLeadingType,
DependentThoroughfareName,
DependentThoroughfareTrailingType,
DependentThoroughfarePostDirection,
Building,
PremisesType,
PremisesNumber,
SubPremisesType,
SubPremisesNumber,
PostBox,
Latitude,
Longitude,
DeliveryIndicator,
MelissaAddressKey,
MelissaAddressKeyBase,
PostOfficeLocation,
SubPremiseLevel,
SubPremiseLevelType,
SubPremiseLevelNumber,
SubBuilding,
SubBuildingType,
SubBuildingNumber,
UTC,
DST,
DeliveryPointSuffix,
CensusKey,
Extras,
JSON_Response

Output Examples#
Verify a single address#
/******************************************************************
Global Address Verification - Single Address Usage Example
******************************************************************/
USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Verify a single address, replace with your values*/
CALL VERIFY_SINGLE_ADDRESS(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,ADDRESS_LINE1          => '22382 Avenida Empresa'
   ,LOCALITY               => 'Rancho Santa Margarita'
   ,ADMINISTRATIVE_AREA    => 'CA'
   ,POSTAL_CODE            => '92688'
   ,COUNTRY                => 'US'
);

Output will be displayed in JSON format.

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. The JSON response will be inserted along side with Default Ouput Fields.

  • Call stored procedure VERIFY_SINGLE_ADDRESS()

    CALL VERIFY_SINGLE_ADDRESS(
  LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
 ,ADDRESS_LINE1          => '22382 Avenida Empresa'
 ,LOCALITY               => 'Rancho Santa Margarita'
 ,ADMINISTRATIVE_AREA    => 'CA'
 ,POSTAL_CODE            => '92688'
 ,COUNTRY                => 'US'
 ,OUTPUT_TABLE_NAME      => '<YOUR_OUTPUT_TABLE_NAME>'
);

  • Query output table

    SELECT *
FROM <YOUR_OUTPUT_TABLE_NAME>;
Verify a single address with selected output fields#

2 default columns will be added automatically: RECORDID and TIMESTAMP

  • Call stored procedure VERIFY_SINGLE_ADDRESS()

    CALL VERIFY_SINGLE_ADDRESS(
  LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
 ,ADDRESS_LINE1          => '22382 Avenida Empresa'
 ,LOCALITY               => 'Rancho Santa Margarita'
 ,ADMINISTRATIVE_AREA    => 'CA'
 ,POSTAL_CODE            => '92688'
 ,COUNTRY                => 'US'
 ,OUTPUT_TABLE_NAME      => '<YOUR_OUTPUT_TABLE_NAME>'
 ,OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
);

  • Query output table

    SELECT *
FROM <YOUR_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>'
  ,ADDRESS_LINE1          => '22382 Avenida Empresa'
  ,LOCALITY               => 'Rancho Santa Margarita'
  ,ADMINISTRATIVE_AREA    => 'CA'
  ,POSTAL_CODE            => '92688'
  ,COUNTRY                => 'US'
  ,OUTPUT_TABLE_NAME      => '<YOUR_OUTPUT_TABLE_NAME>'
  ,OUTPUT_TABLE_FIELDS    => 'All'
);

  • Query output table

    SELECT *
FROM <YOUR_OUTPUT_TABLE_NAME>;

VERIFY_MULTIPLE_ADDRESSES#

The VERIFY_MULTIPLE_ADDRESSES procedure processes address records in batches, validating and standardizing key components using the Global Address Verification 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.

To get started, please contact a sales representative at Snowflakemarketplace@Melissa.com for your License Key.

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.

ORGANIZATION

VARCHAR

Optional.
The organization name associated with the address record.

LASTNAME

VARCHAR

Optional. US Only.
For addresses missing a secondary like a suite or apartment, we can use the last name and append that information for residential addresses.

ADDRESSLINE1

VARCHAR

Required.
The input field for the address.

This should contain the delivery address information (house number, thoroughfare, building, suite, etc.) but should not contain locality information (locality, administrative area, postal code, etc.) which have their own inputs.

ADDRESSLINE2

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE3

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE4

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE5

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE6

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE7

VARCHAR

Optional.
The input field for the address.

ADDRESSLINE8

VARCHAR

Optional.
The input field for the address.

DOUBLEDEPENDENTLOCALITY

VARCHAR

Optional.
The smallest population center data element.

This depends on the Locality and DependentLocality elements.

DEPENDENTLOCALITY

VARCHAR

Optional. Urbanization.
The smaller population center data element.

This depends on the Locality element. In terms of US Addresses, this element applies only to Puerto Rican addresses. It is used to break ties when a ZIP Code™ is linked to multiple instances of the same address.

LOCALITY

VARCHAR

Required. City, Municipality.
The most common population center data element.

SUBADMINISTRATIVEAREA

VARCHAR

Optional. County.
The smallest geographic data element.

ADMINISTRATIVEAREA

VARCHAR

Required. State, Province.
The most common geographic data element.

POSTALCODE

VARCHAR

Required. ZIP, Postcode.
The complete postal code for a particular delivery point.

If all three elements are provided and the PostalCode is incorrect, it can be corrected from the data on the Locality and AdministrativeArea.

SUBNATIONALAREA

VARCHAR

Optional.
The administrative region within a country on an arbitrary level below that of the sovereign state.

COUNTRY

VARCHAR

Required.
The country name, abbreviation, or code.

SQL Script#

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

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) autoincrement start 1 increment 1 noorder
    ,OrganizationName VARCHAR DEFAULT ('')
    ,Address1 VARCHAR DEFAULT ('')
    ,Address2 VARCHAR DEFAULT ('')
    ,Address3 VARCHAR DEFAULT ('')
    ,Address4 VARCHAR DEFAULT ('')
    ,Address5 VARCHAR DEFAULT ('')
    ,Address6 VARCHAR DEFAULT ('')
    ,Address7 VARCHAR DEFAULT ('')
    ,Address8 VARCHAR DEFAULT ('')
    ,City VARCHAR DEFAULT ('')
    ,County 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.

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

  • 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 GLOBAL_ADDRESS_VERIFICATION.CORE;

 /* Set input parameters, replace with your values */
 SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
 SET OPTIONS             = '<OptionName_1:Parameter,OptionName_2:Parameter>';
 SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
 SET OUTPUT_TABLE_NAME   = '<YOUR_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 */
 /* For optional columns, uncomment/comment each line for the needed one. */
 CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
 SELECT
    RecId                                           AS RECORDID
    --,COALESCE(MY_LASTNAME_COL, '')                AS LASTNAME
    ,COALESCE(OrganizationName, '')                 AS ORGANIZATION
    ,COALESCE(Address1, '')                         AS ADDRESSLINE1
    ,COALESCE(Address2, '')                         AS ADDRESSLINE2
    ,COALESCE(Address3, '')                         AS ADDRESSLINE3
    ,COALESCE(Address4, '')                         AS ADDRESSLINE4
    ,COALESCE(Address5, '')                         AS ADDRESSLINE5
    ,COALESCE(Address6, '')                         AS ADDRESSLINE6
    ,COALESCE(Address7, '')                         AS ADDRESSLINE7
    ,COALESCE(Address8, '')                         AS ADDRESSLINE8
    --,COALESCE(MY_DOUBLEDEPENDENTLOCALITY_COL, '') AS DOUBLEDEPENDENTLOCALITY
    --,COALESCE(MY_DEPENDENTLOCALITY_COL, '')       AS DEPENDENTLOCALITY
    ,COALESCE(City, '')                             AS LOCALITY
    ,COALESCE(County, '')                           AS SUBADMINISTRATIVEAREA
    ,COALESCE(State, '')                            AS ADMINISTRATIVEAREA
    ,COALESCE(PostalCode, '')                       AS POSTALCODE
    --,COALESCE(MY_SUBNATIONALAREA_COL, '')         AS SUBNATIONALAREA
    ,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
   ,OPTIONS                => $OPTIONS
   ,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
/**********************************************************************
Global Address Verification - Multiple Addresses Usage Example
**********************************************************************/
/* Grant usage on input table to the application, replace with your values.*/
GRANT USAGE ON DATABASE <INPUT_DATABASE> TO APPLICATION GLOBAL_ADDRESS_VERIFICATION;
GRANT USAGE ON SCHEMA <INPUT_DATABASE>.<INPUT_SCHEMA> TO APPLICATION GLOBAL_ADDRESS_VERIFICATION;
GRANT SELECT ON TABLE <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> TO APPLICATION GLOBAL_ADDRESS_VERIFICATION;

USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Set input parameters, replace with your values */
SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET OPTIONS             = '<OptionName_1:Parameter,OptionName_2:Parameter>';
SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
SET OUTPUT_TABLE_NAME   = '<YOUR_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 */
/* For optional columns, uncomment/comment each line for the needed one. */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
   RecId                                           AS RECORDID
   --,COALESCE(MY_LASTNAME_COL, '')                AS LASTNAME
   ,COALESCE(OrganizationName, '')                 AS ORGANIZATION
   ,COALESCE(Address1, '')                         AS ADDRESSLINE1
   ,COALESCE(Address2, '')                         AS ADDRESSLINE2
   ,COALESCE(Address3, '')                         AS ADDRESSLINE3
   ,COALESCE(Address4, '')                         AS ADDRESSLINE4
   ,COALESCE(Address5, '')                         AS ADDRESSLINE5
   ,COALESCE(Address6, '')                         AS ADDRESSLINE6
   ,COALESCE(Address7, '')                         AS ADDRESSLINE7
   ,COALESCE(Address8, '')                         AS ADDRESSLINE8
   --,COALESCE(MY_DOUBLEDEPENDENTLOCALITY_COL, '') AS DOUBLEDEPENDENTLOCALITY
   --,COALESCE(MY_DEPENDENTLOCALITY_COL, '')       AS DEPENDENTLOCALITY
   ,COALESCE(City, '')                             AS LOCALITY
   ,COALESCE(County, '')                           AS SUBADMINISTRATIVEAREA
   ,COALESCE(State, '')                            AS ADMINISTRATIVEAREA
   ,COALESCE(PostalCode, '')                       AS POSTALCODE
   --,COALESCE(MY_SUBNATIONALAREA_COL, '')         AS SUBNATIONALAREA
   ,COALESCE(Country, '')                          AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
    LICENSE                => $LICENSE
   ,OPTIONS                => $OPTIONS
   ,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#

Parameter

Data Type

Description

Example

LICENSE

VARCHAR

Required.
The License Key with Credits issued by Melissa.

Get it here.

MY_LICENSE_KEY

OPTIONS

VARCHAR

Optional.
[OptionName:Parameter], separate by colon(,)

See more info about OPTIONS.

USPreferredCityNames:on, USStandardizationType:short

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

Optional. Case-sensitive.

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.

Results,
FormattedAddress,
Organization,
AddressLine1,
AddressLine2,
AddressLine3,
AddressLine4,
AddressLine5,
AddressLine6,
AddressLine7,
AddressLine8,
SubPremises,
DoubleDependentLocality,
DependentLocality,
Locality,
SubAdministrativeArea,
AdministrativeArea,
PostalCode,
PostalCodeType,
AddressType,
AddressKey,
SubNationalArea,
CountryName,
CountryISO3166_1_Alpha2,
CountryISO3166_1_Alpha3,
CountryISO3166_1_Numeric,
CountrySubdivisionCode,
Thoroughfare,
ThoroughfarePreDirection,
ThoroughfareLeadingType,
ThoroughfareName,
ThoroughfareTrailingType,
ThoroughfarePostDirection,
DependentThoroughfare,
DependentThoroughfarePreDirection,
DependentThoroughfareLeadingType,
DependentThoroughfareName,
DependentThoroughfareTrailingType,
DependentThoroughfarePostDirection,
Building,
PremisesType,
PremisesNumber,
SubPremisesType,
SubPremisesNumber,
PostBox,
Latitude,
Longitude,
DeliveryIndicator,
MelissaAddressKey,
MelissaAddressKeyBase,
PostOfficeLocation,
SubPremiseLevel,
SubPremiseLevelType,
SubPremiseLevelNumber,
SubBuilding,
SubBuildingType,
SubBuildingNumber,
UTC,
DST,
DeliveryPointSuffix,
CensusKey,
Extras,
JSON_Response

Output Examples#
Verify a table of addresses with the default output fields#

  • Call the stored procedure to verify multiple addresses at once

    USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Set input parameters, replace with your values */
SET LICENSE                 = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET OPTIONS                 = '<OptionName_1:Parameter;OptionName_2:Parameter>';
SET INPUT_TABLE_NAME        = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>';
SET OUTPUT_TABLE_NAME       = '<YOUR_OUTPUT_TABLE_NAME>';

/* Map your input columns with our default schema, replace with your column names */
/* For optional columns, uncomment/comment each line to enable the needed ones. */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
   RECID                                   AS RECORDID,
   --COALESCE(LASTNAME, '')                AS LASTNAME,
   --COALESCE(ORGANZIATION_NAME, '')       AS ORGANIZATION,
   COALESCE(ADDRESS1, '')                  AS ADDRESSLINE1,
   COALESCE(ADDRESS2, '')                  AS ADDRESSLINE2,
   COALESCE(ADDRESS3, '')                  AS ADDRESSLINE3,
   COALESCE(ADDRESS4, '')                  AS ADDRESSLINE4,
   COALESCE(ADDRESS5, '')                  AS ADDRESSLINE5,
   COALESCE(ADDRESS6, '')                  AS ADDRESSLINE6,
   COALESCE(ADDRESS7, '')                  AS ADDRESSLINE7,
   COALESCE(ADDRESS8, '')                  AS ADDRESSLINE8,
   --COALESCE(DOUBLEDEPENDENTLOCALITY, '') AS DOUBLEDEPENDENTLOCALITY,
   --COALESCE(DEPENDENTLOCALITY, '')       AS DEPENDENTLOCALITY,
   COALESCE(CITY, '')                      AS LOCALITY,
   --COALESCE(SUBADMINISTRATIVEAREA, '')   AS SUBADMINISTRATIVEAREA,
   COALESCE(STATE, '')                     AS ADMINISTRATIVEAREA,
   COALESCE(POSTALCODE, '')                AS POSTALCODE,
   --COALESCE(SUBNATIONALAREA, '')         AS SUBNATIONALAREA
   COALESCE(COUNTRY, '')                   AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

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

  • Query output table

    SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);
Verify a table of addresses with selected output fields#

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

    USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Set input parameters, replace with your values */
SET LICENSE                 = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET INPUT_TABLE_NAME        = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>';
SET OUTPUT_TABLE_NAME       = '<YOUR_OUTPUT_TABLE_NAME>';
SET OUTPUT_TABLE_FIELDS     = '<Field_1,Field_2>';

/* Map your input columns with our default schema, replace with your column names */
/* For optional columns, uncomment/comment each line to enable the needed ones. */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
    RECID                                   AS RECORDID,
    --COALESCE(LASTNAME, '')                AS LASTNAME,
    --COALESCE(ORGANZIATION_NAME, '')       AS ORGANIZATION,
    COALESCE(ADDRESS1, '')                  AS ADDRESSLINE1,
    COALESCE(ADDRESS2, '')                  AS ADDRESSLINE2,
    COALESCE(ADDRESS3, '')                  AS ADDRESSLINE3,
    COALESCE(ADDRESS4, '')                  AS ADDRESSLINE4,
    COALESCE(ADDRESS5, '')                  AS ADDRESSLINE5,
    COALESCE(ADDRESS6, '')                  AS ADDRESSLINE6,
    COALESCE(ADDRESS7, '')                  AS ADDRESSLINE7,
    COALESCE(ADDRESS8, '')                  AS ADDRESSLINE8,
    --COALESCE(DOUBLEDEPENDENTLOCALITY, '') AS DOUBLEDEPENDENTLOCALITY,
    --COALESCE(DEPENDENTLOCALITY, '')       AS DEPENDENTLOCALITY,
    COALESCE(CITY, '')                      AS LOCALITY,
    --COALESCE(SUBADMINISTRATIVEAREA, '')   AS SUBADMINISTRATIVEAREA,
    COALESCE(STATE, '')                     AS ADMINISTRATIVEAREA,
    COALESCE(POSTALCODE, '')                AS POSTALCODE,
    --COALESCE(SUBNATIONALAREA, '')         AS SUBNATIONALAREA
    COALESCE(COUNTRY, '')                   AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the verify procedure */
CALL VERIFY_MULTIPLE_ADDRESSES(
     LICENSE                => $LICENSE
    ,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'
);

  • Query output table

    SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);
Verify a table of addresses with ‘ALL’ output fields#

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

    USE GLOBAL_ADDRESS_VERIFICATION.CORE;

/* Set input parameters, replace with your values */
SET LICENSE                 = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET INPUT_TABLE_NAME        = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE>';
SET OUTPUT_TABLE_NAME       = '<YOUR_OUTPUT_TABLE_NAME>';
SET OUTPUT_TABLE_FIELDS     = 'All';

/* Map your input columns with our default schema, replace with your column names */
/* For optional columns, uncomment/comment each line to enable the needed ones. */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
    RECID                                   AS RECORDID,
    --COALESCE(LASTNAME, '')                AS LASTNAME,
    --COALESCE(ORGANZIATION_NAME, '')       AS ORGANIZATION,
    COALESCE(ADDRESS1, '')                  AS ADDRESSLINE1,
    COALESCE(ADDRESS2, '')                  AS ADDRESSLINE2,
    COALESCE(ADDRESS3, '')                  AS ADDRESSLINE3,
    COALESCE(ADDRESS4, '')                  AS ADDRESSLINE4,
    COALESCE(ADDRESS5, '')                  AS ADDRESSLINE5,
    COALESCE(ADDRESS6, '')                  AS ADDRESSLINE6,
    COALESCE(ADDRESS7, '')                  AS ADDRESSLINE7,
    COALESCE(ADDRESS8, '')                  AS ADDRESSLINE8,
    --COALESCE(DOUBLEDEPENDENTLOCALITY, '') AS DOUBLEDEPENDENTLOCALITY,
    --COALESCE(DEPENDENTLOCALITY, '')       AS DEPENDENTLOCALITY,
    COALESCE(CITY, '')                      AS LOCALITY,
    --COALESCE(SUBADMINISTRATIVEAREA, '')   AS SUBADMINISTRATIVEAREA,
    COALESCE(STATE, '')                     AS ADMINISTRATIVEAREA,
    COALESCE(POSTALCODE, '')                AS POSTALCODE,
    --COALESCE(SUBNATIONALAREA, '')         AS SUBNATIONALAREA
    COALESCE(COUNTRY, '')                   AS COUNTRY
FROM IDENTIFIER($INPUT_TABLE_NAME);

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

  • Query output table

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

DROP_OUTPUT_TABLE#

After the creation, output table is only allowed for:

  • Select

    SELECT TOP 100 *
FROM <YOUR_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 GLOBAL_ADDRESS_VERIFICATION.CORE.DROP_OUTPUT_TABLE('<YOUR_OUTPUT_TABLE_NAME>');

Default Ouput Fields#

Column Name

Data Type

RECORDID

NUMBER

MELISSADDRESSKEY

VARCHAR

MELISSADDRESSKEYBASE

VARCHAR

RESULTS

VARCHAR

FORMATTEDADDRESS

VARCHAR

COUNTRYNAME

VARCHAR

LATITUDE

VARCHAR

LONGITUDE

VARCHAR

JSON_RESPONSE

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#