Page 1 of 1
counting number of rows in hash file
Posted: Fri Jun 29, 2007 1:10 pm
by rafik2k
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
Posted: Fri Jun 29, 2007 1:20 pm
by ray.wurlod
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!
Posted: Sat Jun 30, 2007 7:30 am
by rafik2k
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
Posted: Sat Jun 30, 2007 7:50 am
by ray.wurlod
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?
Posted: Sat Jun 30, 2007 7:53 am
by chulett
ray.wurlod wrote:You guys dealing? Why do you need records about hash?
Drug humor.
Posted: Sat Jun 30, 2007 12:25 pm
by rafik2k
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.
Posted: Sat Jun 30, 2007 4:29 pm
by rafik2k
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
Posted: Sat Jun 30, 2007 11:38 pm
by ray.wurlod
That is correct. Account = project. This method adds a VOC entry.