Browse Prior Art Database

Using the SQL COALESCE function to get NLS Messages in the Native Locale or in English if not available

IP.com Disclosure Number: IPCOM000027309D
Original Publication Date: 2004-Apr-07
Included in the Prior Art Database: 2004-Apr-07
Document File: 2 page(s) / 33K

Publishing Venue

IBM

Abstract

Modern internationalized Java-based database programs will often place their message catalogs within the database for easier access rather than use Java ResourceBundles. However, one problem will sometimes surface where a given message is not available in the native locale but is in the English locale. NLS guidelines suggest that in the event there is no such message within the native locale to instead use the English locale. Using the SQL COLAESCE function, one SQL query will suffice to retrieve the native locale message and if not present the English equivalent.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 94% of the total text.

Page 1 of 2

Using the SQL COALESCE function to get NLS Messages in the Native Locale or in English if not available

Two possible queries may be written to retrieve the native locale message string or the English default if none exists:

1. ASSUME: For every MSG_ID possible the table ENGLISH_MSGS will contain one and only one row that contains the MSG_ID and that the MSG_TEXT will either be populated with the text of the message or NULL. If this assumption holds then the query would be:
SELECT

COALESCE (

             FRENCH_MSGS.MSG_TEXT, ENGLISH_MSGS.MSG_TEXT, 'No message for XYZ1234'

)

ENGLISH_MSGS

LEFT OUTER JOIN

    FRENCH_MSGS (ON FRENCH_MSGS.MSG_ID = ENGLISH_MSGS.MSG_ID)

WHERE

ENGLISH_MSGS.MSG_ID = 'XYZ1234';

-- OR --

2. ASSUME: For every MSG_ID possible the table PROGRAM_MSGS will be a one column table (msg_id) which will contain one row for MSG_ID. Then the ENGLISH_MSGS and FRENCH_MSGS tables can contain 0 rows and the query would be:
SELECT

COALESCE (

             FRENCH_MSGS.MSG_TEXT, ENGLISH_MSGS.MSG_TEXT, 'No message for XYZ1234'

FROM

)FROM

PROGRAM_MSGS

LEFT OUTER JOIN

    FRENCH_MSGS (ON FRENCH_MSGS.MSG_ID = PROGRAM_MSGS.MSG_ID)

1

Page 2 of 2

LEFT OUTER JOIN

    ENGLISH_MSGS (ON ENGLISH_MSGS.MSG_ID = PROGRAM_MSGS.MSG_ID)

WHERE

PROGRAM_MSGS.MSG_ID = 'XYZ1234';

These examples are written for DB2 database systems and may vary with other vendor database systems. For performance reasons we add the MSG_ID= clause to both of the ON CLAUSES. The order of the languages in case 2 should make no difference. If eit...