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
counting number of rows in hash file
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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!
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.
Thanks Ray for your input!
I have tried testing a job, but I got following error message
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
I have tried testing a job, but I got following error message
I checked the hash file HASHRECORD, it exists in the specified path.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.|
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:
You guys dealing? Why do you need records about hash?
Code: Select all
SETFILE /dir/dir/.../HASHRECORD HASHRECORD
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.
Code: Select all
Ray: It can be created with SETFILE
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: