Problem Note 18285: Duplicate column names across lookup tables might result in incorrect lookup results
If multiple lookup tables, referenced as input to a Lookup
transformation, contain the same 'lookup output' variable name, then
incorrect output data values may be generated in the Lookup target
table. The following example demonstrates the issue.
If you have two lookup tables, both with a 'lookup output' variable
named KEY.
Lookup1 contains:
Lookup2 contains:
LookupInput table contains:
ACCTNO USERID
111 abc
111
xyz
222 xyz
|
And you have the following Lookup mappings defined:
Source > Lookup Tables > LookupTarget
ACCTNO > ACCTNO > KEY > ACCTKEY
USERID > USERID > KEY > USERKEY
|
LookupTarget will contain the following:
ACCTKEY USERKEY
1 3
1 1 <-- Should be missing, but gets ACCTKEY value instead!
4
2 4
|
If the first lookup finds a match during the lookup process, the result
gets returned into the 'lookup output' variable (KEY) for this lookup
definition and this gets assigned to the first Target variable
(ACCTKEY). If the second lookup does NOT find a match during the lookup
process, no value is returned into the 'lookup output' variable (KEY
again) for this lookup definition. However, the return value from the
earlier lookup is still present, so this same value now gets assigned to
the second Target variable when it should have received a missing and/or
blank value.
This is only one of multiple issues that may be encountered when
duplication exists between Lookup Input, Lookup Tables, and Lookup
Output.
The best way to avoid issues is to ensure uniqueness among variable
names across all tables. Often this can be accomplished through
renaming columns, either permanently or temporarily within the job
flow.
Select the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| SAS System | SAS Data Integration Studio | Microsoft Windows NT Workstation | 3.3 | 4.2 | 9.1 TS1M3 SP3 | 9.1 TS1M3 SP3 |
| Microsoft Windows XP Professional | 3.3 | 4.2 | 9.1 TS1M3 SP3 | 9.2 TS2M0 |
| Microsoft Windows 2000 Server | 3.3 | 4.2 | 9.1 TS1M3 SP3 | 9.2 TS2M0 |
| Microsoft Windows Server 2003 Datacenter Edition | 3.3 | 4.2 | 9.1 TS1M3 SP3 | 9.2 TS2M0 |
| Microsoft Windows Server 2003 Enterprise Edition | 3.3 | 4.2 | 9.1 TS1M3 SP3 | 9.2 TS2M0 |
| Microsoft Windows Server 2003 Standard Edition | 3.3 | 4.2 | 9.1 TS1M3 SP3 | 9.2 TS2M0 |
| Microsoft Windows 2000 Advanced Server | 3.3 | 4.2 | 9.1 TS1M3 SP3 | 9.2 TS2M0 |
| Microsoft Windows 2000 Datacenter Server | 3.3 | 4.2 | 9.1 TS1M3 SP3 | 9.2 TS2M0 |
| Microsoft Windows 2000 Professional | 3.3 | 4.2 | 9.1 TS1M3 SP3 | 9.2 TS2M0 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Type: | Problem Note |
| Priority: | alert |
| Date Modified: | 2006-08-31 14:47:08 |
| Date Created: | 2006-08-15 13:54:58 |
