DSXchange

Hello everybody,

I hope you can help me. In my job I use a hashfile as source which have to be sorted at the beginning without using a Sort-Stage. My question is now, is it possible to sort a hashfile via command defined as "Before Job Routine"? I found a command like SORT.ITEM, but it doesn't save the sorted result in the hashfile.

Thanks for your reply

Best Regards
BK

You can use a UV stage to access the hash file and then use 'normal' SQL commands to extract the data from it - including the ORDER BY clause. However, it can't be stored sorted... it's a hashed file after all.

Where is your hash file located? If it's in your project (i.e. account) then it will be fairly easy. If you are using pathed hash files, then you'll need to create a VOC record first before the UV stage can be used.

All of this has been covered in the forum, so searching should turn up more information on this subject.

This is easy with a little trick.

Let's say we have a column names length and another named width, and we want to sort our data in ascending order by length and within each length in descending order by width.

On the selection tab of the hash file stage, code the following: The @ID = @ID is just a "dummy" criteria saying the row's primary key equals itself. The BY keyword is an ascending order sort. The BY.DSND is a descending order sort.

For the selection tab to be enabled in your hash file stage, the hash file must be created in the project directory or an account. If this is not the case, you must create a VOC pointer to the hash file, and then you can treat it as if the hash file is in the project directory.

One way to create VOC pointers is by using my CreateVocPtr routine. You can find it on the DataStage Tools page of www.anotheritco.com .

Thanks for your fast reply!

It is a pathed hashfile with a pointer (set by "SETFILE"), so IT works :D

Good idea with the "dummy join"

Interesting. Have to file this one away.

I have the VOC pointer and can see the selection Tab on the Hash File stage but I cannot click it. Its like locked, what can be the reason.
Thanks
Sam

You CANNOT use the WITH trick for ordering data if it's an externally pathed hash file. It has to be a local account, so remove the fully qualified directory path and use a blank project/account name. It will then use the VOC pointer entry to find the path to the hash file.

Check on your General tab. Even though you have a VOC pointer, perhap you have the Use Directory Path button pressed.

kcbland wrote:You CANNOT use the WITH trick for ordering data if it's an externally pathed hash file. It has to be a local account, so remove the fully qualified directory path and use a blank project/account name. It will then use the VOC pointer entry to find the path to the hash file.

You can prove this very easily. As soon as "use directory path" is selected, the Selection tab on the Output (link properties) is disabled. Select "account" and the Selection tab is enabled. It's on the Selection tab where you put the BY and BY.DSND phrases.