Sample reference data queries

AD
DM
DS
ST

Reference data information is available in tables starting with reference. Translations are in the reference_lang table and codes broken down by type and country are in the reference table.

Note: Reference fields are stored as codes, so queries sorted by reference fields are sorted by the code value, rather than by a particular translation. The reporting result viewer displays reference fields in your preferred language.

Note: The reference language code for Icelandic is IS, which is also a SQL operator. If you create a SQL query, to ensure that your Icelandic language query validates, add double quotes (") around the IS field. This applies anywhere you reference the field in the query (statements, clauses, and so on).

Active US codes with selected translations

This example provides address codes that are active in the US, along with their English, French, and Spanish translations.

SELECT
        r.code,
        en,
        fr,
        es
    FROM
        reference r LEFT JOIN reference_lang l
            ON r.code = l.code
    WHERE
        country = 'US'
        AND active = 'true'
        AND r.reference_type = 'AddressAdminArea'
    ORDER BY
        r.code ;

Sample results:

Localized reference data in entity report

This example creates a report that display results in a particular language. You can join reference columns in your entity query to reference data.

SELECT
        vid__v,
        last_name__v,
        hcp_status__v,
        specialty_code.fr AS specialty_1_french
    FROM
        hcp JOIN reference_lang specialty_code
            ON specialty_code.code = specialty_1__v
    WHERE
        specialty_1__v = 'FM' ;

Sample results:

Extract reference codes and labels in the same report

The following SQL query uses a JOIN statement to extract reference codes and labels for a reference field. This is useful in mapping reference values in drop down lists to downstream systems.

SELECT hcp.specialty_1__v,
       reference_lang.en
FROM hcp
  INNER JOIN reference_lang ON specialty_1__v = code AND reference_type = 'Specialty'

Note: You can only extract reference codes and labels in the same report using the SQL query editor.

In the report results, the two columns appear duplicated, but you can see the corresponding reference codes by placing the cursor over the reference label.

To extract both the reference labels and codes in the results, you must download the report and export the code values.

To ensure that both reference labels and codes appear in the downloaded report, specify Codes when you download the report.

The spreadsheet will contain columns for both, based on the SQL query in the example.

Reference codes and labels for multiple reference fields

This example creates a report from which you can download reference codes and labels for multiple reference fields. As with the previous example, only the labels are shown in the initial report results in the Network UI.

SELECT hcp.specialty_1__v,
       r1.en,
       hcp.hcp_type__v,
       r2.it
FROM hcp
  INNER JOIN reference_lang r1 ON hcp.specialty_1__v = r1.code AND r1.reference_type = 'Specialty'
  INNER JOIN reference_lang r2 ON hcp.hcp_type__v = r2.code AND r2.reference_type = 'HCPType'

Sample results:

Using the same process to download the report as in the previous example, you will see both columns in the resulting spreadsheet.