Hash file lookup

ak77 · Post by **ak77** » Thu Dec 15, 2005 6:50 am

hi everybody,

i dont know if any of you had this situation
i am sure someone or other has done this before
i have to do a lookup between a sequential file and hash file

I have only one field that need to be equal and there is a date that needs to be between two date fields in the hashfile

the table from which i load the hash file has 3 key columns
while loading if i just select one column as the key, it is removing duplicates based on this single field

If i use the table direclty as lookup, it is selecting the first match and so the data gets rejected for date constraint

Can someone help me with this?

Thanks

Kishan

rbhudsuder · Post by **rbhudsuder** » Thu Dec 15, 2005 7:01 am

Hi,

Isn't the "date that needs to be between two date fields in the hashfile" always the same date during one run of the job? In that case you can filter the hashed file by the date and create another (temporary) hashed file which is keyed only by the first (non-date) column, and use that for the lookup.

If the date is not constant within one run, then it is probably not the way to go.
Good luck with DataStage.

Marton

ak77 · Post by **ak77** » Thu Dec 15, 2005 7:14 am

Thanks for the quick response

the sequential file has a number field and a date field
the hash file has 2 number fileds and 2 date fields

I need the seq_file.NUmber = hashfile.Number1 and seq_file.date between hashfile.date1 and hashfile.Date2, if this condiiton satisfies i need to get the hashfile.number2

Isn't the "date that needs to be between two date fields in the hashfile" always the same date during one run of the job? In that case you can filter the hashed file by the date and create another (temporary) hashed file which is keyed only by the first (non-date) column, and use that for the lookup.

i didnt get this, can you please explain a little bit more

thanks again

kishan

dls · Post by **dls** » Thu Dec 15, 2005 7:57 am

Search the forum by 'multirow' or 'multi-row'.

Ken Bland has offered a solution that ought to work for you.

ridar · Post by **ridar** » Thu Dec 15, 2005 10:23 pm

Hi,

For date range comparisons the best way to use is "Table Look up" where you have only the Number1 column as key and in the where clause you will have it like
WHERE
Table.Number1 = :1
AND
seq_file.date between Table.date1 and Table.Date2.

Try this and let us know if they work.

Ridar.

ak77 · Post by **ak77** » Fri Dec 16, 2005 1:04 pm

Thanks Ridar,

I am dealing with Server Job version 6.0
Its possible in Mainframe Job and Parallel job where they have Lookup stage where you can do this

WHERE
Table.Number1 = :1
AND
seq_file.date between Table.date1 and Table.Date2.

I am not sure if it is possible in Server Job
May be its possible , if so can you or somebody tell me how to do this?

Thanks
Kishan

ray.wurlod · Post by **ray.wurlod** » Fri Dec 16, 2005 2:48 pm

It IS possible in a server job, however it's not possible with a Hashed File stage (where you must lookup the key, the whole key, and exactly the key).

You can use a UV stage to refer to the hashed file and do SQL-style lookups, but this won't be fast. It can be improved by creating indexes on the hashed file, particularly on the date column on which you're constraining.

ak77 · Post by **ak77** » Fri Dec 16, 2005 4:49 pm

Thanks Ray,

I will try that and see if it helps me

Regards,
Kishan

ray.wurlod · Post by **ray.wurlod** » Sat Dec 17, 2005 4:21 pm

You will need to create a VOC pointer to a directory-pathed hashed file. Search the forum for SETFILE.