Using Aliases When Tables Have Extended Key Structure

Normally, you only use database aliases when a field is on a table that is keyed by some combination of employee ID, effective date, and plan. If there are additional keys, you would not normally be able to use a database alias. You've already looked at one exception to this rule, the JOB table. There is also a workaround for getting to other inappropriately keyed tables.

When a table has another key structure, you can create an appropriately keyed view with the needed information. For example, the benefit program participation table, BEN_PROG_PARTIC, is keyed by employee ID, record number, COBRA event ID, and effective date.

To create an alias to the BENEFIT_PROGRAM field on this table, you create a view containing only three fields: the allowable keys, EMPLID and EFFDT, and the field you need to access, BENEFIT_PROGRAM.

The select statement for your view does the work of getting the right row when there are multiple rows with the same EMPLID and EFFDT—in this case, by only selecting records where the employee record number is zero and the COBRA event ID is blank.

The SQL statement in this example is:

SELECT EMPLID,

EFFDT,

BENEFIT_PROGRAM

FROM PS_BEN_PROG_PARTIC

WHERE EMPL_RCD#=0

AND COBRA_EVENT_ID = ' '

Now, to set up an alias for this field, you reference the view instead of the original table.