Sample reference data queries
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 ;
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' ;
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'
Using the same process to download the report as in the previous example, you will see both columns in the resulting spreadsheet.