How can I do a dynamic select?
Moderators: chulett, rschirm, roy
How can I do a dynamic select?
This is my problem:
I have the following file(sequencial or hashed):
cod:
1
2
3
And I need to do a select based on these numbers..
for instance:
select * from table where cod = ?
<cod> comes from my file (it's an index).
and so, I will put these results in a file.
How can I built somethig to do that??
NOTES:
*I can't unload my database to a hashed file cause its's too big(more than 200 million).
I tried:
DATABASE
ACCESS
TRANSFORMER FILE
FILE
But it seems don't work....
Anyone have an idea to do it or somothing like it (with a good performance)?
I have the following file(sequencial or hashed):
cod:
1
2
3
And I need to do a select based on these numbers..
for instance:
select * from table where cod = ?
<cod> comes from my file (it's an index).
and so, I will put these results in a file.
How can I built somethig to do that??
NOTES:
*I can't unload my database to a hashed file cause its's too big(more than 200 million).
I tried:
DATABASE
ACCESS
TRANSFORMER FILE
FILE
But it seems don't work....
Anyone have an idea to do it or somothing like it (with a good performance)?
Tiago
Re: How can I do a dynamic select?
You have mentioned that your Code can be in a sequential or hashed file. Assuming it is in a hashed file (as reference), you can stream your database input and do a lookup in the Xfmr. All you need to do is put the constraint Not(IsNull(HashLink.Code)) in your output link.
gateleys
gateleys
I think what he wants to do gateleys is to pass that cod as a job parameter to the select statement to just select those records which satisfy the where clause.
Tiagogen:
You need to build a unix script to read those numbers one by one and pass it as a job parameter to the dsjob call.
That will give you the best performance as you are just selecting the records you need.
Make sure that the job parameter is parth of your target file name as well. This way for each run of the select, a different file will be created, either prefixed or suffixed by the cod.
Tiagogen:
You need to build a unix script to read those numbers one by one and pass it as a job parameter to the dsjob call.
That will give you the best performance as you are just selecting the records you need.
Make sure that the job parameter is parth of your target file name as well. This way for each run of the select, a different file will be created, either prefixed or suffixed by the cod.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 36
- Joined: Thu Sep 01, 2005 5:44 am
- Location: Canada
DSguru2B wrote:I think what he wants to do gateleys is to pass that cod as a job parameter to the select statement to just select those records which satisfy the where clause.
Tiagogen:
You need to build a unix script to read those numbers one by one and pass it as a job parameter to the dsjob call.
That will give you the best performance as you are just selecting the records you need.
Make sure that the job parameter is parth of your target file name as well. This way for each run of the select, a different file will be created, either prefixed or suffixed by the cod.
DSguru What if i want to pass all the values in the file at one shot to the query like
select * from table where cod in (1,2,3...)
Thanks & Regards,
Rony
Rony
DSGuru haan. You promoted me
Well if my server was on unix then i would read that file and build a parameter file. In the job , where i am doing a select, pass the 1,2,3.. as a job parameter.
If you want to do it via a routine and job sequence, then i would write a routine that would just opens the file containing 1,2,3...., read it and pass it as a job parameter to the next job that contains the select.
and my sql select would be
where ValRange is the job parameter that will have the values 1,2,3...
Hope i Answered your question.
Well if my server was on unix then i would read that file and build a parameter file. In the job , where i am doing a select, pass the 1,2,3.. as a job parameter.
If you want to do it via a routine and job sequence, then i would write a routine that would just opens the file containing 1,2,3...., read it and pass it as a job parameter to the next job that contains the select.
and my sql select would be
Code: Select all
select * from table where cod in (#ValRange#)
Hope i Answered your question.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 36
- Joined: Thu Sep 01, 2005 5:44 am
- Location: Canada
Well, you really should have started a new post. Because this is a server forum and your question is for a PX job.
Anywho, If you want to all this in one job then you can do it utilizing a little bit of scripting.
the parameterfile.txt will have the value
-param ValRange=1,2,3....
Basically you will already have the parameterfile.txt with the value for ValRange. You will pass that as a parameter to the dsjob command. And it will dynamically select values from the table.
About the routine, can have a simple routine that just opens the file containing the values 1,2,3....
basically the routine will be something like
You can add more error handling to the code. Arg1 will be the complete absolute path of the file that contains the values 1,2,3...
Hope it helps.
Regards,
Anywho, If you want to all this in one job then you can do it utilizing a little bit of scripting.
Code: Select all
$JOB_PARAM = `cat /dir/dir/dir/parameterfile.txt`
....dsjob -run $JOB_PARAM <ProjectName> <JobName>
-param ValRange=1,2,3....
Basically you will already have the parameterfile.txt with the value for ValRange. You will pass that as a parameter to the dsjob command. And it will dynamically select values from the table.
About the routine, can have a simple routine that just opens the file containing the values 1,2,3....
basically the routine will be something like
Code: Select all
DEFFUN ReadVal(Arg1) CALLING "DSU.ReadParameter"
Ans = ""
ValRangeFile = ""
ValRangeFile = Arg1
OpenSeq ValRangeFile To fCfg
Else Call DSLogFatal("Error opening file ":ValRangeFile)
ReadSeq Line From fCfg then
Ans = Line
CloseSeq fCfg
RETURN(Ans)
Hope it helps.
Regards,
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 36
- Joined: Thu Sep 01, 2005 5:44 am
- Location: Canada
Sorry about that. Since tiagogen has already done with (i guess ) I thought we will continue in the same thread as it will be easier to communicate the idea. Yes but then the forum is totaly different...
My parameterfile.txt is not a sequential file but it is a data set. So the code $JOB_PARAM = `cat /dir/dir/dir/parameterfile.txt` won't help. What needs to be done in that case for a ds file?
I will certainly give a try with the routine. But i am still not clear as to how the return value of the routine will be passed to the parameter (#ValRange#) in my query.[/code]
My parameterfile.txt is not a sequential file but it is a data set. So the code $JOB_PARAM = `cat /dir/dir/dir/parameterfile.txt` won't help. What needs to be done in that case for a ds file?
I will certainly give a try with the routine. But i am still not clear as to how the return value of the routine will be passed to the parameter (#ValRange#) in my query.[/code]
Thanks & Regards,
Rony
Rony
-
- Participant
- Posts: 36
- Joined: Thu Sep 01, 2005 5:44 am
- Location: Canada
Sorry about that. Since tiagogen has already done with (i guess ) I thought we will continue in the same thread as it will be easier to communicate the idea. Yes but then the forum is totaly different...
My parameterfile.txt is not a sequential file but it is a data set. So the code $JOB_PARAM = `cat /dir/dir/dir/parameterfile.txt` won't help. What needs to be done in that case for a ds file?
I will certainly give a try with the routine. But i am still not clear as to how the return value of the routine will be passed to the parameter (#ValRange#) in my query.
My parameterfile.txt is not a sequential file but it is a data set. So the code $JOB_PARAM = `cat /dir/dir/dir/parameterfile.txt` won't help. What needs to be done in that case for a ds file?
I will certainly give a try with the routine. But i am still not clear as to how the return value of the routine will be passed to the parameter (#ValRange#) in my query.
Thanks & Regards,
Rony
Rony
well you will need to get that value from a dataset and put it in a sequential file.
As per the routine, you can do that in a sequence job. In the properties of job activity, click on the Value Expression of the Parameter ValRange and click on Routine and there you can call your routine. The output of that routine will automatically be the value of the job parameter.
As per the routine, you can do that in a sequence job. In the properties of job activity, click on the Value Expression of the Parameter ValRange and click on Routine and there you can call your routine. The output of that routine will automatically be the value of the job parameter.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 36
- Joined: Thu Sep 01, 2005 5:44 am
- Location: Canada
But what if the job is not a seqence job?DSguru2B wrote:well you will need to get that value from a dataset and put it in a sequential file.
As per the routine, you can do that in a sequence job. In the properties of job activity, click on the Value Expression of the Parameter ValRange and click on Routine and there you can call your routine. The output of that routine will automatically be the value of the job parameter.
Thanks & Regards,
Rony
Rony
Then make one. A simple job sequence that executes one job and goes to a notification activity or something.
From within the job you cannot set and use a job parameter. You will have to set that explicitly from outside the job.
Thats the limitation.
Regards,
From within the job you cannot set and use a job parameter. You will have to set that explicitly from outside the job.
Thats the limitation.
Regards,
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.