Joins over look up functions

[mageshwaran_subramanian]

I often see look up functions is being used when performing value mapping. I see there are some disadvantages using the look up functions over joins

1.Visibility.When you review a job to fix or change the mapping rule, it hard to identify where the lookup has been used. If lookup is done using joins it’s easy for the programmers to locate the mapping.

2. Handling duplicate data .When there's duplicate in the look up table, it’s not safe to use look up function, when do so, it simply return one of the value.Say you are looking for a new material type from the look up table, what happens if it contains two different new material type for an old material type? It returns one of the new material type based on the return policy you specified (Max/Min).When a join is used, and if a duplicate is found as given in the above scenario then both the values will be returned and it can be identified by looking at the result set.

3. Picking more than one column from the look up table. The value return by the look up function can be mapped to only one column. But a join can return more than one column and can be mapped to more than one column in the same query transform

4. Slower performance. There’s a greater possibility that a join can be pushed down rather than a look up function used within a query transform