Selecting records with 1:N relat. from hash file lookup?

[PilotBaha]

I have been using hash files with no problem when the relationship was 1:1. Looks like in cases where I have 1:N I am not getting all the possible records I need to get if I was trying to get an inner join. Is there a way to get the output from a 1:N relationship between stream and hash files ?

Let me try to explain the situation with an example:


Thanks...

Code: Select all

CUST No           CUST NAME
----------           --------------
1234                 ABC Inc. 


CUST No           CUST STATE
1234                 NY
1234                 IL

When I do the lookup I get only one record
1234 ABC Inc. NY

instead of the both combinations.
1234 ABC Inc. NY
1234 ABC Inc. IL

[chulett]

I have been using hash files with no problem when the relationship was 1:1. Looks like in cases where I have 1:N I am not getting all the possible records I need to get if I was trying to get an inner join. Is there a way to get the output from a 1:N relationship between stream and hash files ?

No... that's not the way that hash files work. You only get one record when you do the keyed lookup because that's all that is in the hash file! Hash files are 'destructive overwrite' by nature, based on the keys fields and last one in wins. When you wrote that second record with the same keys to the hash it replaced the first record.

You are going to need to use a relational table (accessed via ODBC) or a UV table to get multiple records back from a single lookup.