Sorting Hashfile possible?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 13
- Joined: Thu Nov 04, 2004 4:30 am
Sorting Hashfile possible?
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
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
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 .
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
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 .
Chuck Smith
www.anotheritco.com
www.anotheritco.com
-
- Participant
- Posts: 13
- Joined: Thu Nov 04, 2004 4:30 am
-
- Participant
- Posts: 25
- Joined: Wed Oct 13, 2004 1:11 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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Premium Member
- Posts: 385
- Joined: Wed Jun 16, 2004 12:43 pm
- Location: Virginia, USA
- Contact:
Check on your General tab. Even though you have a VOC pointer, perhap you have the Use Directory Path button pressed.
Chuck Smith
www.anotheritco.com
www.anotheritco.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.