I am bit confused on Hash look up function and look up funtion in Optim.They both seem to serve the same purpose but with a different mechanism.It would be helpful if you could give a brief description on when to use has look up and when to use look up.
Pinned topic Difference b/w Hash Look Up and Look Up function
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-06-08T14:44:49Z at 2012-06-08T14:44:49Z by OptimDev
Ducatiman 27000213JK59 Posts
Re: Difference b/w Hash Look Up and Look Up function2012-06-06T23:26:49ZThis is the accepted answer. This is the accepted answer.Use Lookup function to perform an exact match and substitute the value of a column
for example you have a table that has 2 digit state code (Ca for California etc.)
and you want to populate a column with the full name of the State. You could use the lookup function to lookup using the state code and populate the State column with the full name of the state.
The lookup table would look like:
STATECODE | STATE
CA | California
FL | Florida
TX | Texas
lookup(statcd, privacy.lookup_states(STATECODE, STATE))
I mostly use Hash_lookups which require an input 'key' to derive a number corresponding to a row in a lookup table. Let's say I use the social security number field as an input to a hash lookup function, the function will then generate a number (between -3 to the number of records in the lookup table) and grab whatever fields you want out of the lookup table on that row. Using SSN as the seed will generate say, the number 454, then it will go to the Optim lookup table and go down to row 454 and grab the data you want.
As long as you use the same seed it will always translate to 454 (in this example).
This means that using this function on all the databases you mask on any platform will always grab the same informatio for that particualr record (or SSN rather). So you've masked the record consistently across all your databases.
But you already knew this didn't you ;)
SEQ | Name | address |...
1 |john smith |... |
2 |sue jones |... |
3 |allan martin |.... |