Key fields may be numeric, character or date fields. Join supports a variety of key comparisons between two tables (character to numeric, numeric to numeric, character to character and date to date).
Normally, keys are of the same type, but when they are different, Join will attempt to harmonize them. Join will only attempt to harmonize keys when the same number of key fields have been selected from each table. When multiple keys fields are selected they will be harmonized in pair sets; first primary key with first secondary key, etc.
In the case of character to numeric key comparisons, Analyzer will prompt the user to auto-harmonize the disparate keys, and if accepted, Analyzer will convert the character key to a numeric key.
In the case of character to character key comparisons where the key lengths differ, Analyzer will prompt the user to auto-harmonize the keys, and if accepted, the length of the shorter character key will be padded with the appropriate number of blanks.
Date to Date key field comparisons don’t require auto-harmonization as all dates are stored in a consistent internal date format.
Date to Numeric or Date to Character comparisons are technically allowed but are not auto-harmonized. These comparisons will most likely yield no results and so should be avoided.
In any case, auto-harmonizing may result in unintended Join results. The alternative, if your key fields are not identical, is to manually harmonize them using a variety of useful Analyzer functions which can convert the data type, structure or length of the keys to make them suitable for use as key fields.
Tip: To convert numeric fields to character format, see STRING(). To convert positive numeric fields to a character format with leading zeros see ZONED(). To alter the length of a character field, see SUBSTRING(). To convert character fields containing only numeric digits into numeric fields, see VALUE(). Also see Expression Builder and Functions details on other functions you can use to harmonize your key fields.