Creating Views for One Base Table and One Related Language Table

When you have a view that selects data from a single table, the SELECT statement for the related language view is straightforward: select the data from the related language table making sure to also select LANGUAGE_CD.

The following diagram shows DEPT_TBL with one key column (DEPT_ID), one language-sensitive column (DESCR), and one column that is not language sensitive (MGR_ID). DEPT_TBL has a one-to-many relationship with DEPT_TBL_LANG, a table that includes DEPT_ID, LANGUAGE_CD, and DESCR:

This diagram illustrates views of one base table, one related language table

As an example, DEPT_TBL contains the following data:

DEPT_ID	DESCR	MGR_ID
110	Finance	01732
120	Engineering	22056
131	Sales - Belgium	08630
132	Sales - Germany	08630
133	Sales - UK	12972
134	Sales - Japan	28553

The related language table, DEPT_TBL_LANG, contains the following data:

DEPT_ID	LANGUAGE_CD	DESCR
110	FRA	Finances
110	GER	Finanzierung
120	FRA	Technologie
120	GER	Technik
131	FRA	Ventes - Belgique
132	GER	Verkäufe - Deutschland
133	GER	Verkäufe - GB

The following is the SELECT statement for the base view:

SELECT A.DEPT_ID, A.DESCR FROM DEPT_TBL A

The following is the SELECT statement for the related language view:

SELECT B.DEPT_ID, B.DESCR, B.LANGUAGE_CD FROM DEPT_TBL_LANG B

Because this base view is simple and selects only columns that exist both in the base table and in the related language table, the related language view is straightforward. It differs from the base view in only two ways:

• The name of the table from which it selects.

• The addition of the LANGUAGE_CD column.

If the base view also selected non-key, non-language-sensitive columns from the base table, those columns would need to be selected from the base table in the related language view because non-key, non-language-sensitive fields don’t exist in related language records.