Passing a List of Values Into SQL as Parameter
Moderators: chulett, rschirm, roy
Passing a List of Values Into SQL as Parameter
Good Day:
I have a server DataStage job that reads from a DATABASE table base on the SQL below:
SELECT C1,C2,C3 From Table where TYPE IN('A','B','C','D', etc...etc)
The "IN" list can be rather long and to avoid typing in all the possible types in the SQL, is there a way of putting these values in a file and have the server job process the list from a file as a parameter of some sort?
If using a file is possible, how do I pass this file name into the SQL?
Thanks In Advance:
dtatem
I have a server DataStage job that reads from a DATABASE table base on the SQL below:
SELECT C1,C2,C3 From Table where TYPE IN('A','B','C','D', etc...etc)
The "IN" list can be rather long and to avoid typing in all the possible types in the SQL, is there a way of putting these values in a file and have the server job process the list from a file as a parameter of some sort?
If using a file is possible, how do I pass this file name into the SQL?
Thanks In Advance:
dtatem
Re: Passing a List of Values Into SQL as Parameter
1. Define a job parameter called 'ListOfValues' in the job.dtatem wrote:Good Day:
I have a server DataStage job that reads from a DATABASE table base on the SQL below:
SELECT C1,C2,C3 From Table where TYPE IN('A','B','C','D', etc...etc)
The "IN" list can be rather long and to avoid typing in all the possible types in the SQL, is there a way of putting these values in a file and have the server job process the list from a file as a parameter of some sort?
If using a file is possible, how do I pass this file name into the SQL?
Thanks In Advance:
dtatem
2. Change SQL to SELECT C1,C2,C3 From Table where TYPE IN(#ListOfValues#)
3. Create a file (FileWithValues)and list the values in a single line, in the format 'A','B','C','D' and so on.
4. At the sequence level, use a Execute Command stage to return the line with cat FileWithValues.
5. Pass the output of step 4 to the job containing the SQL.
6. Assign the ReturnValue of the Execute Command stage to the ListOfValues job parameter.
You are ready to go.
Alternatively, you can use the UserVariable stage to store the list of values, and pass this variable to the ListOfValues job parameter. This way you don't need to use the file.
gateleys
Re: Passing a List of Values Into SQL as Parameter
Thanks for replying..I like your idea of using the UserVariable Stage...a question about this, will I have to list all of values in the UserVariable stage??
So am I to understand if I use the UserVariableStage to store all the values, when the job runs, via the sequencer all values in that list will get process?? Am I right??
Thanks
--dtatem
So am I to understand if I use the UserVariableStage to store all the values, when the job runs, via the sequencer all values in that list will get process?? Am I right??
Thanks
--dtatem
gateleys wrote:1. Define a job parameter called 'ListOfValues' in the job.dtatem wrote:Good Day:
I have a server DataStage job that reads from a DATABASE table base on the SQL below:
SELECT C1,C2,C3 From Table where TYPE IN('A','B','C','D', etc...etc)
The "IN" list can be rather long and to avoid typing in all the possible types in the SQL, is there a way of putting these values in a file and have the server job process the list from a file as a parameter of some sort?
If using a file is possible, how do I pass this file name into the SQL?
Thanks In Advance:
dtatem
2. Change SQL to SELECT C1,C2,C3 From Table where TYPE IN(#ListOfValues#)
3. Create a file (FileWithValues)and list the values in a single line, in the format 'A','B','C','D' and so on.
4. At the sequence level, use a Execute Command stage to return the line with cat FileWithValues.
5. Pass the output of step 4 to the job containing the SQL.
6. Assign the ReturnValue of the Execute Command stage to the ListOfValues job parameter.
You are ready to go.
Alternatively, you can use the UserVariable stage to store the list of values, and pass this variable to the ListOfValues job parameter. This way you don't need to use the file.
Re: Passing a List of Values Into SQL as Parameter
Yes.dtatem wrote:Thanks for replying..I like your idea of using the UserVariable Stage...a question about this, will I have to list all of values in the UserVariable stage??
Well, that is the purpose, isn't it? The list that you input in the UserVariable stage should be passed to the parameter of the job containing the SQL. Don't forget to replace the actual list of values in your SQL with the name of job param enclosed by #.dtatem wrote: So am I to understand if I use the UserVariableStage to store all the values, when the job runs, via the sequencer all values in that list will get process?? Am I right??
gateleys
Re: Passing a List of Values Into SQL as Parameter
I have attempted to use the UserVariablestage in aSequencer
UserVariables----------->JobActivity
In the UserVariables stage, in the first column I have "ListVals" . In the second column I have the values listed as "A","B","C"..but it appears that it doesn't like that for the values because it is saying "Expected: End of Expression at the bottom in red
When I use one value in the second column, the expected:end of expression error goes away. Please let me know what I am doing wrong
thanks
--dtatem
UserVariables----------->JobActivity
In the UserVariables stage, in the first column I have "ListVals" . In the second column I have the values listed as "A","B","C"..but it appears that it doesn't like that for the values because it is saying "Expected: End of Expression at the bottom in red
When I use one value in the second column, the expected:end of expression error goes away. Please let me know what I am doing wrong
thanks
--dtatem
gateleys wrote:Yes.dtatem wrote:Thanks for replying..I like your idea of using the UserVariable Stage...a question about this, will I have to list all of values in the UserVariable stage??
Well, that is the purpose, isn't it? The list that you input in the UserVariable stage should be passed to the parameter of the job containing the SQL. Don't forget to replace the actual list of values in your SQL with the name of job param enclosed by #.dtatem wrote: So am I to understand if I use the UserVariableStage to store all the values, when the job runs, via the sequencer all values in that list will get process?? Am I right??
I have no idea if this would work properly by the time it gets to your SQL, but try a string where your inside quotes are just data:
Double quotes on each end, single quotes around each letter. Or if your SQL needs double quotes, reverse them. Of course, your whole point of using a file has been lost along the way and you might as well type 'the list' right into the job parameter at this point.
Code: Select all
"'A','B','C'"
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
gateleys
In my UserVariables stage, you are right, I needed double quotes, but now when the job runs from the sequencer, it only processes one of the list values and not the rest.
In my UserVariables stage, you are right, I needed double quotes, but now when the job runs from the sequencer, it only processes one of the list values and not the rest.
chulett wrote:I have no idea if this would work properly by the time it gets to your SQL, but try a string where your inside quotes are just data:
Double quotes on each end, single quotes around each letter. Or if your SQL needs double quotes, reverse them. Of course, your whole point of using a file has been lost along the way and you might as well type 'the list' right into the job parameter at this point.Code: Select all
"'A','B','C'"
Can you check the log as to how the SQL is being interpreted by DataStage? And if you can post that SQL here.dtatem wrote:gateleys
In my UserVariables stage, you are right, I needed double quotes, but now when the job runs from the sequencer, it only processes one of the list values and not the rest.
gateleys
Gateleys:
The SQL is fine...thinks are running fine now...
--dtatem
The SQL is fine...thinks are running fine now...
--dtatem
gateleys wrote:Can you check the log as to how the SQL is being interpreted by DataStage? And if you can post that SQL here.dtatem wrote:gateleys
In my UserVariables stage, you are right, I needed double quotes, but now when the job runs from the sequencer, it only processes one of the list values and not the rest.
Re: Passing a List of Values Into SQL as Parameter
Dear dtatem,
I followed the steps specified below.
In Output of Execute Command stage,
I get two data:
Reply=0
Output from command ====>
xyz(this is the required data).
My problem is how do i pass this output as specified in step 5 of the Quote??
Pls help.
Thanks in advance
I followed the steps specified below.
In Output of Execute Command stage,
I get two data:
Reply=0
Output from command ====>
xyz(this is the required data).
My problem is how do i pass this output as specified in step 5 of the Quote??
Pls help.
Thanks in advance
gateleys wrote:1. Define a job parameter called 'ListOfValues' in the job.dtatem wrote:Good Day:
I have a server DataStage job that reads from a DATABASE table base on the SQL below:
SELECT C1,C2,C3 From Table where TYPE IN('A','B','C','D', etc...etc)
The "IN" list can be rather long and to avoid typing in all the possible types in the SQL, is there a way of putting these values in a file and have the server job process the list from a file as a parameter of some sort?
If using a file is possible, how do I pass this file name into the SQL?
Thanks In Advance:
dtatem
2. Change SQL to SELECT C1,C2,C3 From Table where TYPE IN(#ListOfValues#)
3. Create a file (FileWithValues)and list the values in a single line, in the format 'A','B','C','D' and so on.
4. At the sequence level, use a Execute Command stage to return the line with cat FileWithValues.
5. Pass the output of step 4 to the job containing the SQL.
6. Assign the ReturnValue of the Execute Command stage to the ListOfValues job parameter.
You are ready to go.
Alternatively, you can use the UserVariable stage to store the list of values, and pass this variable to the ListOfValues job parameter. This way you don't need to use the file.
Re: Passing a List of Values Into SQL as Parameter
Dear dtatem,
I followed the steps specified below.
In Output of Execute Command stage,
I get two data:
Reply=0
Output from command ====>
xyz(this is the required data).
My problem is how do i pass this output as specified in step 5 of the Quote??
Pls help.
Thanks in advance
I followed the steps specified below.
In Output of Execute Command stage,
I get two data:
Reply=0
Output from command ====>
xyz(this is the required data).
My problem is how do i pass this output as specified in step 5 of the Quote??
Pls help.
Thanks in advance
gateleys wrote:1. Define a job parameter called 'ListOfValues' in the job.dtatem wrote:Good Day:
I have a server DataStage job that reads from a DATABASE table base on the SQL below:
SELECT C1,C2,C3 From Table where TYPE IN('A','B','C','D', etc...etc)
The "IN" list can be rather long and to avoid typing in all the possible types in the SQL, is there a way of putting these values in a file and have the server job process the list from a file as a parameter of some sort?
If using a file is possible, how do I pass this file name into the SQL?
Thanks In Advance:
dtatem
2. Change SQL to SELECT C1,C2,C3 From Table where TYPE IN(#ListOfValues#)
3. Create a file (FileWithValues)and list the values in a single line, in the format 'A','B','C','D' and so on.
4. At the sequence level, use a Execute Command stage to return the line with cat FileWithValues.
5. Pass the output of step 4 to the job containing the SQL.
6. Assign the ReturnValue of the Execute Command stage to the ListOfValues job parameter.
You are ready to go.
Alternatively, you can use the UserVariable stage to store the list of values, and pass this variable to the ListOfValues job parameter. This way you don't need to use the file.
Re: Passing a List of Values Into SQL as Parameter
Dear dtatem,
I followed the steps specified below.
In Output of Execute Command stage,
I get two data:
Reply=0
Output from command ====>
xyz(this is the required data).
My problem is how do i pass this output as specified in step 5 of the Quote??
Pls help.
Thanks in advance
I followed the steps specified below.
In Output of Execute Command stage,
I get two data:
Reply=0
Output from command ====>
xyz(this is the required data).
My problem is how do i pass this output as specified in step 5 of the Quote??
Pls help.
Thanks in advance
gateleys wrote:1. Define a job parameter called 'ListOfValues' in the job.dtatem wrote:Good Day:
I have a server DataStage job that reads from a DATABASE table base on the SQL below:
SELECT C1,C2,C3 From Table where TYPE IN('A','B','C','D', etc...etc)
The "IN" list can be rather long and to avoid typing in all the possible types in the SQL, is there a way of putting these values in a file and have the server job process the list from a file as a parameter of some sort?
If using a file is possible, how do I pass this file name into the SQL?
Thanks In Advance:
dtatem
2. Change SQL to SELECT C1,C2,C3 From Table where TYPE IN(#ListOfValues#)
3. Create a file (FileWithValues)and list the values in a single line, in the format 'A','B','C','D' and so on.
4. At the sequence level, use a Execute Command stage to return the line with cat FileWithValues.
5. Pass the output of step 4 to the job containing the SQL.
6. Assign the ReturnValue of the Execute Command stage to the ListOfValues job parameter.
You are ready to go.
Alternatively, you can use the UserVariable stage to store the list of values, and pass this variable to the ListOfValues job parameter. This way you don't need to use the file.
Re: Passing a List of Values Into SQL as Parameter
Dear dtatem,
I followed the steps specified below.
In Output of Execute Command stage,
I get two data:
Reply=0
Output from command ====>
xyz(this is the required data).
My problem is how do i pass this output as specified in step 5 of the Quote??
Pls help.
Thanks in advance
I followed the steps specified below.
In Output of Execute Command stage,
I get two data:
Reply=0
Output from command ====>
xyz(this is the required data).
My problem is how do i pass this output as specified in step 5 of the Quote??
Pls help.
Thanks in advance
gateleys wrote:1. Define a job parameter called 'ListOfValues' in the job.dtatem wrote:Good Day:
I have a server DataStage job that reads from a DATABASE table base on the SQL below:
SELECT C1,C2,C3 From Table where TYPE IN('A','B','C','D', etc...etc)
The "IN" list can be rather long and to avoid typing in all the possible types in the SQL, is there a way of putting these values in a file and have the server job process the list from a file as a parameter of some sort?
If using a file is possible, how do I pass this file name into the SQL?
Thanks In Advance:
dtatem
2. Change SQL to SELECT C1,C2,C3 From Table where TYPE IN(#ListOfValues#)
3. Create a file (FileWithValues)and list the values in a single line, in the format 'A','B','C','D' and so on.
4. At the sequence level, use a Execute Command stage to return the line with cat FileWithValues.
5. Pass the output of step 4 to the job containing the SQL.
6. Assign the ReturnValue of the Execute Command stage to the ListOfValues job parameter.
You are ready to go.
Alternatively, you can use the UserVariable stage to store the list of values, and pass this variable to the ListOfValues job parameter. This way you don't need to use the file.
dtatem, since your problem has been addressed, can you please mark the post as 'Resolved'?dtatem wrote:Gateleys:
The SQL is fine...thinks are running fine now...
--dtatem
And, may be help our friend loe_ram13 with his query? I have already provided the solution, which he doesn't seem to get it. May be you could help him with your words.
gateleys