DSXchange

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

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

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

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

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

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.

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

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.

Thanks Ray,

I will try that and see if it helps me


Regards,
Kishan

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