counting number of rows in hash file

rafik2k · Post by **rafik2k** » Fri Jun 29, 2007 1:10 pm

Hi,
My requirement to fire sql query in a hash file and get record count based on where condition.

For example,
Select count(*) from hash_file where record_status='Active'

where record_status is column name

If you have any idea/suggestion, kindly let me know.

thanks in advance

ray.wurlod · Post by **ray.wurlod** » Fri Jun 29, 2007 1:20 pm

Use a UV stage with a single Integer column whose Derivation is COUNT(*). Table name is the name of your hashed file (you may need to create a VOC pointer). The Selection tab contains your WHERE clause. Voila!

rafik2k · Post by **rafik2k** » Sat Jun 30, 2007 7:30 am

Thanks Ray for your input!
I have tried testing a job, but I got following error message

COUNT_RECORD_IN_HASHFILE..Sequential_File_2.IDENT1: |COUNT_RECORD_IN_HASHFILE..UniVerse_1.DSLink4: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:SELECT COUNT(*) FROM HASHRECORD
SQLSTATE=S0002, DBMS.CODE=950390
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: Table "HASHRECORD" does not exist.|

I checked the hash file HASHRECORD, it exists in the specified path.

Can you please let me know where I am going wrong?
Also can you please explain how to create VOC pointer for this?

Thanks in advance

ray.wurlod · Post by **ray.wurlod** » Sat Jun 30, 2007 7:50 am

You're not going wrong anywhere - it's just that the hashed file was created in a directory, so there's no VOC record pointing to it. Queries require the VOC pointer. It can be created with SETFILE:

Code: Select all

SETFILE /dir/dir/.../HASHRECORD HASHRECORD

You guys dealing? Why do you need records about hash?

chulett · Post by **chulett** » Sat Jun 30, 2007 7:53 am

ray.wurlod wrote:You guys dealing? Why do you need records about hash?

Drug humor.

rafik2k · Post by **rafik2k** » Sat Jun 30, 2007 12:25 pm

Thanks Ray!
Ray,Craig: This is my requirement where I want to count no. of records reading from the source(in may case hashed file) and compare with records count loaded into target database db2.

I am reusing the hashed which was created by other job.

rafik2k · Post by **rafik2k** » Sat Jun 30, 2007 4:29 pm

Code: Select all

Ray: It can be created with SETFILE

Thanks a lot Ray!
Using SETFILE command,I set a VOC pointer of the file and
It's working fine

But when I use account name insted of directory path for hashed file, I don't need to set it

Thanks

ray.wurlod · Post by **ray.wurlod** » Sat Jun 30, 2007 11:38 pm

That is correct. Account = project. This method adds a VOC entry.