DSXchange

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

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.

Thank you for your answer Ray and Noted the terminology


Thanks
kris