what derivation to be given to hash file key

sangvi · Post by **sangvi** » Wed Mar 23, 2005 9:18 pm

Hi ,
I need to have a reference lookup with a hash which has two keys fields .One key field is a counter and another is the field that finds matching rows with the database table.Actually this counter field is not matched with anyof the columns in the table and so is a dummy .
If this the case what derivation should i pass to the counter column so that my lookup should happen only considering another key whereas keeping this counter key as dummy?
My work is struck up with this part as i m not aware of what derivation to be passed so that to make datastage understand the specified columns is a dummy column.

Thanks in advance.

Regards,
Sangvi

kcbland · Post by **kcbland** » Wed Mar 23, 2005 9:28 pm

You cannot do what you want. A hash file is not a database table. The primary key is fixed for a row, it determines where the row is placed in the file and must be known in order to retrieve that row by reference. Using a portion of a multi-column key is nonetheless a different key, and you won't find your row.

Please state what your purpose or problem is functionally, so that we can provide a technical solution for you.

sangvi · Post by **sangvi** » Wed Mar 23, 2005 9:46 pm

Hi Kcbland,

there is four columns in sequential filesay

name datatype
col1 varchar
col2 numeric
col3 date
col4 numeric

for the above i used a aggregator to find the summing col4.all before using aggregator i added a column counter and made that column as a key so that col4 is grouped by col1,col2,col3. I inserted a column counter cos i need to take all the values from col1,col2,col3....

the result of the aggregator is given to a hash file as below

name
counter key
col1 key
col2
col3
col4

now this above hash file is referenced with database table with col1 to find the matching fields.when i do this the counter is another key is showing me in red for passing some derivations.I have to actually consider this column as a dummy as i dont have any matching column in my database table.

The above said is the design by me how well i can do the above design so that i have to take all the values from col1 though there are duplicate values existing.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 23, 2005 10:00 pm

There's nothing wrong with the design. It just can't be done with a hashed file (other than in its guise as a UV table).

Accessing a hashed file via the Hashed File uses the key, the entire key. It is not equipped to use partial key searches. The entire key (that is, the physical representation of the combination of key column values) is input for the hashing algorithm that determines the physical location of the record whose key that is.

sangvi · Post by **sangvi** » Wed Mar 23, 2005 10:04 pm

with my design i have an extra column that waits for a derivation to be passed when called from reference link....is there no dummy derivation that i can call to make datastage understand it is a dummy column..

or else how sud i process this job ..this is one requirement from my client.
help me out as it dosnt struck me any other way to carry out this.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 23, 2005 10:10 pm

You can use a UV stage to access the hashed file. Declare only col1 to be Key; declare counter not to be Key.
In the Transformer stage check the "multi-row return" check box.

This will not be fast. To get some speed you would need to create an index on col1 in the hashed file.

There is no reason to use a hashed file here. You could use any database table, with the same metadata and the same technique and the same requirement to index for speed.

sangvi · Post by **sangvi** » Wed Mar 23, 2005 11:59 pm

Thanks ray
All the suggestions were really helpful to me.Resumed working.

Regards,
sangvi