UtilityHashLookup question

weaver · Post by **weaver** » Tue Jun 06, 2006 12:23 pm

Hello,

I am having trouble with my UtilityHashLookup routine.

The code I have is

DEFFUN UtilityHashLookup(A1,A2,A3) Calling "DSX.UTILITYHASHLOOKUP"

TA1 = TRIM(A1)
TA2 = TRIM(A2)
TA3 = TRIM(A3)

Ans = UtilityHashLookup(TA1,TA2,TA3)

to A1, I pas the name of my account path saved hash file
to A2, the key of the hash file which is the code key I am looking up
to A3, I pass "2"

The hash file contains 2 columns. both trimmed for spaces.
col 1, the code key I am looking up
col 2, the text description of the code key
I have designated col 1 as the key by checking "key" on the stage.

When I execute the code, I always get back "**RECORD NOT FOUND**" When I look at the data, I can't see anything that would make my key code different from the string I'm passing into A2.

Any ideas what I should investigate next?
Thanks for any help you can offer.

kcbland · Post by **kcbland** » Tue Jun 06, 2006 12:51 pm

Your key is position 0, the attribute you need is position 1. Try passing a "1" in the third argument.

By the way, welcome aboard.

weaver · Post by **weaver** » Tue Jun 06, 2006 12:59 pm

Thank you for your response.

I get the same "**RECORD NOT FOUND**" regardless of what I pass in the 3rd argument. 1, 2, A, blank, it doesn't seem to matter.

I do get a switch from "RECORD" not found to "TABLE" not found if I passed the wrong hash file name.

`` Emily

kcbland · Post by **kcbland** » Tue Jun 06, 2006 1:04 pm

The comments inside the utility function are:

Code: Select all

* Executes a lookup against a hashed file using a key
*
* Input Parameters :   Arg1 = Hash Table Name
*                      Arg2 = Hash Key Value
*                      Arg3 = Column Position to return.
*                             If empty, return entire row in a dynamic array.

Arg3, if left blank, returns the entire row. If the row is found, Arg3 is evaluated to the position (ignoring the key) in the row as an array and that value located there is returned.

Your error message indicates that the hashed file does not contain a row using the key provided. You're trimming, are you case-specific as well? Since this utility uses a project specific hashed file, make sure your job writes the hashed file in the same project you're using when testing your function.

DSguru2B · Post by **DSguru2B** » Tue Jun 06, 2006 1:47 pm

How many keys are defined in the hashed file. I am running a bunch of tests at my end. When i try to run that utility on a hashed file with one key, it works fine, but when i run it with a hashed file with 2 or more keys, it gives me the same "**RECORD NOT FOUND**" message. You think that might be the problem, if ofcourse your hashed file has 2 or more keys.

weaver · Post by **weaver** » Tue Jun 06, 2006 1:48 pm

The key code values in the hash file are in all caps as are the arguments I am entering. No match is found even if I "view data", copy and then paste into my routine. I can't see any extra characters anywhere.

I also tried calling the UtilityHashLookup directory in a transformer stage (instead of creating a routine) and received the same result.

Checking the forums, I did realize that this is a project specific utility so I had made sure that hash file and the Routine both live in the same project.

However, I just noticed that I had typed in the account name in the "use account name" option of the hash file. On a whim, I left it blank this time and re-created the hash file. To my eye, the hash file appears in the same location as before when I view through ssh to the directory. However, this time, DataStage was able to find the matching record. Typing in "projectName" is apparently different than allowing DataStage assign "projectName". I guess I tried to make too much sure that they were in the same project.

Thanks for your help. I've been having issues with this for three days.

kcbland · Post by **kcbland** » Tue Jun 06, 2006 1:51 pm

You should have joined the forum sooner... :wink:

Leaving the "account" (project) blank just defaults the hashed file to the current project.

DSguru2B · Post by **DSguru2B** » Tue Jun 06, 2006 1:57 pm

Ken, So if a hashed file has two keys defined, how can we specify that in the utility then?

weaver · Post by **weaver** » Tue Jun 06, 2006 2:01 pm

Thanks again for your help with the UtilityHashLookup. This is my company's first project with DataStage so I didn't really have an opportunity to join (much) sooner. Only 3 days :lol:

I knew that blank defaulted to the current project, but I thought I'd be sure and obvious by typing in the current project name. Apparently that was a bad idea.

kcbland · Post by **kcbland** » Tue Jun 06, 2006 2:10 pm

DSguru2B wrote:Ken, So if a hashed file has two keys defined, how can we specify that in the utility then?

You have to pass that to the function concatenated together using an @TM delimiter. A multi-part key would look like:

Code: Select all

UtilityHashLookup("",key1:@TM:key2:@TM:key3, "")

kcbland · Post by **kcbland** » Tue Jun 06, 2006 2:14 pm

weaver wrote:Thanks again for your help with the UtilityHashLookup. This is my company's first project with DataStage so I didn't really have an opportunity to join (much) sooner. Only 3 days

I knew that blank defaulted to the current project, but I thought I'd be sure and obvious by typing in the current project name. Apparently that was a bad idea.

If you don't have any IBM/Ascential consulting onsite to help or train, consider getting your company to pay for a Premium Membership to unlock the whole content on this site. Checkout the newsletters to see the things coming the way of our members: video tips and tricks, audio answers, premium download stuff, etc. Not to mention all the quick and detailed help from the Premium Posters

DSguru2B · Post by **DSguru2B** » Tue Jun 06, 2006 2:20 pm

Wow. I did'nt know that. Thats great. But one confusion.

Code: Select all

UtilityHashLookup("",key1:@TM:key2:@TM:key, "")

Say i had two keys.
key1--->understood
key2--->understood
key----->confused :?:

What is key supposed to be?
Is it key1 and key2 concatenated together?

kcbland · Post by **kcbland** » Tue Jun 06, 2006 2:54 pm

Supposed to be key3, I fixed the original post.

DSguru2B · Post by **DSguru2B** » Tue Jun 06, 2006 3:03 pm

Ok. Now it makes sense. Thanks Ken. :lol: