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
Hash file lookup
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 8
- Joined: Mon Aug 09, 2004 3:49 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
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
thanks again
kishan
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
i didnt get this, can you please explain a little bit moreIsn'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.
thanks again
kishan
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
May be its possible , if so can you or somebody tell me how to do this?
Thanks
Kishan
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
I am not sure if it is possible in Server JobWHERE
Table.Number1 = :1
AND
seq_file.date between Table.date1 and Table.Date2.
May be its possible , if so can you or somebody tell me how to do this?
Thanks
Kishan
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: