Universe or Basic command : Select query on Hashfile

[caltog]

I would like to retrieve a column in a hashfile by specifying a key in the where clause, such as :

Code: Select all

SELECT * FROM H_MYFILE WHERE KEY_1 = Arg1

The hashfile is containing global parameters and is created in the Directory Path.

If try try to execute LIST.DICT HF_MYFILE, I am getting an error "Table xxx does not exist"

How can I simply execute a select query and return a row from a Hashfile in a Job Routine or a Job Control ?

Thanks

[ArndW]

You cannot issue a DataStage SELECT or an SQL SELECT on a hashed file that is only referred to by path. In order to do this you need to create a file type record in your VOC. One way to do this is to use the TCL command

Code: Select all

SETFILE {pathname} {filename} {OVERWRITING}

which will create a file pointer that you can use to access the hashed file.

[ray.wurlod]

Or, if it's from a routine (as your post requests) and you want just one record (which your SQL suggests), open the pathed hashed file with OPENPATH and read the record with READ.

That way you avoid the overhead of loading the query engine into memory.

[ArndW]

Ray's recommendation is by far the more efficient one, if your hashed file has just one key and it is "KEY_1". Otherwise without a dictionary or VOC pointer you would need to read through the whole file using OPENPATH to get the pointer, SELECT to select all the records in the file and loop around a READ statement (plus a final CLOSE to cleanly finish).

[ray.wurlod]

Even if it has more than one key, you just assemble the key column values with intervening text marks (@TM).

[ArndW]

I looked at the post and thought that most likely this will return more than one row unless KEY_1 is the only key; which is why a (single) READ wouldn't be a solution. We'll see what the original poster says and take it from there.

[caltog]

Hi Ray, Hi ArnW,

I finally solve my problem by writing a routine following your information.

I am using OPENPATH, READ, SELECT, and I am able to read the column needed. (In fact I have two keys, and concatening them works perfectly.)

Thanks for your precious tips !!

[ArndW]

Caltog - good news

Ray - I guess Caltog's response answered my previous post's question