Passing a List of Values Into SQL as Parameter

[dtatem]

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

[gateleys]

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

1. Define a job parameter called 'ListOfValues' in the job.
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]

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

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

1. Define a job parameter called 'ListOfValues' in the job.
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]

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??

Yes.

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??

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]

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

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??

Yes.

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??

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 #.

[chulett]

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:

Code: Select all

"'A','B','C'"

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.

[dtatem]

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.

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:

Code: Select all

"'A','B','C'"

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.

[gateleys]

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.

Can you check the log as to how the SQL is being interpreted by DataStage? And if you can post that SQL here.

[dtatem]

Gateleys:

The SQL is fine...thinks are running fine now...

--dtatem

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.

Can you check the log as to how the SQL is being interpreted by DataStage? And if you can post that SQL here.

[loe_ram13]

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

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

1. Define a job parameter called 'ListOfValues' in the job.
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.

[loe_ram13]

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

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

1. Define a job parameter called 'ListOfValues' in the job.
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.

[loe_ram13]

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

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

1. Define a job parameter called 'ListOfValues' in the job.
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.

[loe_ram13]

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

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

1. Define a job parameter called 'ListOfValues' in the job.
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.

[chulett]

Wow... a Quadruple Hijack! Haven't seen that before - that gets you a 10.0 from this judge! :D

Assign $ReturnValue<1> to the job parameter. Use the ellipsis (...) to start it off rather than attempt to hand type the whole thing.

[gateleys]

Gateleys:

The SQL is fine...thinks are running fine now...

--dtatem

dtatem, since your problem has been addressed, can you please mark the post as 'Resolved'?


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.