complex key expressions in a has file lookup

[jlegare]

Let's say I want to do a theta join (a BETWEEN type join) on a hash lookup file. Is it possible and if so how?

I have a table that has a zone column. I also have a hash file that has three columns, zone_low, zone_high and region. I want the hash file join to lookup region based on a theta join.

I want the hash file join do do the equivalent of following:

select
table.zone,
hash.region
from
table, region
where
table.zone BETWEEN hash.low_zone AND hash.high_zone


I could do this lookup in SQL on the database but want to see if it is possible to do such a thing in the hash file.


Also - is it possible to get full outer join behaviour from a hash file join? If so how?

Thank you in advance


J.

[ray.wurlod]

:D Welcome to the Forum!

What you're seeking to do can't be done with a hashed file stage, which uses a computation (called a hashing algorithm) to determine the location of a record based solely on its primary key value.

A search of the Forum using the keywords "hashed file" and BETWEEN yielded quite a number of hits, as this question has been asked before. One of the answers can be found at viewtopic.php?t=86417

Searching for all terms "hashed file", BETWEEN and UV provided a more closely selected set of posts that may help you.

In quick summary, you CAN use a hashed file, in its guise as a UniVerse table, accessing it via the UV stage type (and user-defined SQL therein). You may also need to index the search columns to get acceptable performance.

UniVerse/SQL does not, however, support RIGHT OUTER JOIN or FULL OUTER JOIN.