Sorting Hashfile possible?

branimir.kostic · Post by **branimir.kostic** » Wed Dec 01, 2004 7:54 am

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

chulett · Post by **chulett** » Wed Dec 01, 2004 8:16 am

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.

chucksmith · Post by **chucksmith** » Wed Dec 01, 2004 8:17 am

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:

Code: Select all

@ID = @ID BY length BY.DSND width

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 .

branimir.kostic · Post by **branimir.kostic** » Wed Dec 01, 2004 8:30 am

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"

chulett · Post by **chulett** » Wed Dec 01, 2004 9:18 am

Interesting.

Have to file this one away.

coolkhan08 · Post by **coolkhan08** » Wed Dec 01, 2004 10:05 am

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

kcbland · Post by **kcbland** » Wed Dec 01, 2004 10:10 am

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.

chucksmith · Post by **chucksmith** » Wed Dec 01, 2004 10:11 am

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

ray.wurlod · Post by **ray.wurlod** » Wed Dec 01, 2004 2:38 pm

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.