Select localized values for a property

The locale function is used to select localized values from the source object. The locale function is case-sensitive, and all letters are lower-case. The number of positions defines the number of numbered columns that are returned in the results. The locale function returns localized values that are based on the locale selections.

The query results are the property name with a position number suffix, such as property_1, property_2, property_3. The values that are populated in the positions are the localized values matching the locale code, or a search list locale code, for each position.

If the data object does not contain localized values, the property’s string value is returned in position. The remaining position columns are empty.

Syntax

SELECT locale(localizedproperty, number of positions) FROM object

Example 1

In this example, the Data Catalog has five locale selections, 1 for en_US, 2 for es_ES, 3 for fr_FR, 4 for cz_cz, and 5 for nl_NL. If five positions are selected, these values are returned:

  • greeting_1 for English
  • greeting_2 for Spanish
  • greeting_3 for French
  • greeting_4 for Czech
  • greeting_5 for Dutch 
SELECT Id, locale(greeting,5) from common_greetings

This table shows an example:

Id greeting_1 greeting_2 greeting_3 greeting_4 greeting_5

1

hello

hola

bonjour

ahoj

hallo

Example 2

In this example, the Data Catalog has only three locale selections: 1 for en_US, 2 for fr_FR, and 3 for es_ES. If five positions are selected, these values are returned

  • greeting_1 for English
  • greeting_2 for Spanish
  • greeting_3 for French

greeting_4 and greeting_5 are null because no locales are defined for positions 4 and 5. 

SELECT Id, locale(greeting,5) from common_greetings

This table shows an example:

Id greeting_1 greeting_2 greeting_3 greeting_4 greeting_5

1

hello

hola

bonjour

You can also query the data object to show all the localized strings using the locale codes as the suffix of the column names.

You can also view the localized strings with the locale suffixes using a select * query. To achieve this, you can use the includeInSelectAll query hint in combination with the L parameter.

See Query hints

If the Data Catalog Locale Selections change, it may be necessary to clear the data object’s Compass data storage and Compass object definitions. When new locales are added to the Data Catalog, such as Portuguese and Italian, clear the object definition using the Infor Clear Table stored procedure. This process clears the object definition that includes the original locales but not the new locales of Portuguese and Italian.

It may also be necessary to clear the Compass data. If historic objects contain localized strings for Portuguese and Italian, clear the data. To clear the Compass data, use the Infor Clear Table with the option to clear reformatted data. Alternatively, use the Infor Clear Data option to clear Compass data. The next time a query runs, the Portuguese and Italian strings in the data are populated.

Alternatively, if the historic data does not include Portuguese and Italian strings, it does not have to be cleared. Queries for historic data have NULL values for Portuguese and Italian.