HASH file WITH clause

[vam]

Hi Ds guru's,

we use hash files to point to UV and pull the data into our staging area. we have FILE1 source (pointer1) and FILE 2 lookup (pointer 2), In the present design we select all the records from FILE1 and make a lookup with File 2 to find the delta records. is there a better way we can do it by using WITH clause in the hash file (source file). i tried using activity varable in the sequencer and passing it to source(FILE1) but am hitting limit of 32000 key values ( key is 25 byte long). we will get more than 32,000 records for a given point of time..

My question is can we use something like a "in clause" in universe that we use in oracle

the keys in both the file are same.. FILE 2 only contains key values and timestamp

can we do something like below..
SELECT SOURCE FILE WITH SELECT FILE2

Thank you in advance..

Kris

[ray.wurlod]

First off, the correct terminology is "hashed" file.

You can certainly use a WITH clause, though without the WITH preposition, in the Selection tab of a Hashed File stage.

However, what you are trying to do is not a valid syntax for a WITH clause. SELECT is a verb and FILE2 is a file name, neither of which can be used in a WITH clause.

You would be better off using a UniVerse stage using user-defined SQL and naming the join explicitly.

Another method would be to use a before-stage subroutine (ExecUV) to create a numbered Select List from FILE2 then specify the SLIST option in the SQL. If you want to use a Hashed File stage, use the before-stage subroutine to generate the default Select List from FILE2; the query generated in the Hashed File stage will use this automatically.

[vam]

Thank you for your answer Ray and Noted the terminology


Thanks
kris