Hi, I have to use variables in one of my INPUT stages. Data will be
selected according to 2 variables
select *
from product
where company = variable_1
and client = variable_2
..... but variable_1 & variable_2 still has to get values, so I tried (user defined sql, using a user defined routine)
select *
from product
where company = CalculateCompany(parameter)
and client = CalculateClient(parameter)
This doesnt work. How / where can I calculate the variables and how can I use them in the input stage ?
*****
I read about Stage variables, but they are displayed on the output side of the transform stage, so I dont understand how they actually work. I dont know if this is the key to my problem.
Im actually doubt that, because in the transform, Im already past the input stage, and there is no constraint option on the input side of the transform.
Dirk Moolman
Database Administrator
Reach Technologies
South Africa
www.thefuelgroup.co.za
"No pessimist ever discovered the secret of the stars, or sailed to an uncharted land, or opened a new doorway for the human spirit." -Helen Keller
==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
Using variables
Are you defining the variables once per job (ie. they get set at the outset of the job and remain persistant)? If so, you will want to pass these in as parameters (go to the Edit / Job Properties menu and click on the parameters tab). Once you create the parameter, you can refer to it in your sql by surrounding the parameter with the "#" sign (as in "#variable1#").
If you are saying that you want these values to change as the job is running, I will need more detail about what you are trying to accomplish.
Dirk Moolman wrote: Hi, I have to use variables in one of my INPUT stages. Data will be selected according to 2 variables
select *
from product
where company = variable_1
and client = variable_2
..... but variable_1 & variable_2 still has to get values, so I tried (user defined sql, using a user defined routine)
select *
from product
where company = CalculateCompany(parameter)
and client = CalculateClient(parameter)
This doesnt work. How / where can I calculate the variables and how can I use them in the input stage ?
*****
I read about Stage variables, but they are displayed on the output side of the transform stage, so I dont understand how they actually work. I dont know if this is the key to my problem.
Im actually doubt that, because in the transform, Im already past the input stage, and there is no constraint option on the input side of the transform.
Dirk Moolman
Database Administrator
Reach Technologies
South Africa
www.thefuelgroup.co.za
"No pessimist ever discovered the secret of the stars, or sailed to an uncharted land, or opened a new doorway for the human spirit." -Helen Keller
==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
---------------------------------
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
If you are saying that you want these values to change as the job is running, I will need more detail about what you are trying to accomplish.
Dirk Moolman wrote: Hi, I have to use variables in one of my INPUT stages. Data will be selected according to 2 variables
select *
from product
where company = variable_1
and client = variable_2
..... but variable_1 & variable_2 still has to get values, so I tried (user defined sql, using a user defined routine)
select *
from product
where company = CalculateCompany(parameter)
and client = CalculateClient(parameter)
This doesnt work. How / where can I calculate the variables and how can I use them in the input stage ?
*****
I read about Stage variables, but they are displayed on the output side of the transform stage, so I dont understand how they actually work. I dont know if this is the key to my problem.
Im actually doubt that, because in the transform, Im already past the input stage, and there is no constraint option on the input side of the transform.
Dirk Moolman
Database Administrator
Reach Technologies
South Africa
www.thefuelgroup.co.za
"No pessimist ever discovered the secret of the stars, or sailed to an uncharted land, or opened a new doorway for the human spirit." -Helen Keller
==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
---------------------------------
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
I got one idea from someone I just phoned.
He says that for variables to be available in an input stage, it must be passed to the job as a parameter. Variables cannot be calculated in an input stage. I will have to run a control job which will calculate the parameter,
and then pass it to the next job
.... and in the next job I can process the input stage using a user
defined sql, eg.
select *
from product
where company = parameter_1
and client = parameter_2
I am going to try this right now.
-----Original Message-----
From: Dirk Moolman [mailto:dirkm@reach.co.za]
Sent: Thursday, October 25, 2001 3:32 PM
To: Datastage List
Subject: Using variables
Hi, I have to use variables in one of my INPUT stages. Data will be
selected according to 2 variables
select *
from product
where company = variable_1
and client = variable_2
..... but variable_1 & variable_2 still has to get values, so I tried (user defined sql, using a user defined routine)
select *
from product
where company = CalculateCompany(parameter)
and client = CalculateClient(parameter)
This doesnt work. How / where can I calculate the variables and how can I use them in the input stage ?
*****
I read about Stage variables, but they are displayed on the output side of the transform stage, so I dont understand how they actually work. I dont know if this is the key to my problem.
Im actually doubt that, because in the transform, Im already past the input stage, and there is no constraint option on the input side of the transform.
Dirk Moolman
Database Administrator
Reach Technologies
South Africa
www.thefuelgroup.co.za
"No pessimist ever discovered the secret of the stars, or sailed to an uncharted land, or opened a new doorway for the human spirit." -Helen Keller
==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
He says that for variables to be available in an input stage, it must be passed to the job as a parameter. Variables cannot be calculated in an input stage. I will have to run a control job which will calculate the parameter,
and then pass it to the next job
.... and in the next job I can process the input stage using a user
defined sql, eg.
select *
from product
where company = parameter_1
and client = parameter_2
I am going to try this right now.
-----Original Message-----
From: Dirk Moolman [mailto:dirkm@reach.co.za]
Sent: Thursday, October 25, 2001 3:32 PM
To: Datastage List
Subject: Using variables
Hi, I have to use variables in one of my INPUT stages. Data will be
selected according to 2 variables
select *
from product
where company = variable_1
and client = variable_2
..... but variable_1 & variable_2 still has to get values, so I tried (user defined sql, using a user defined routine)
select *
from product
where company = CalculateCompany(parameter)
and client = CalculateClient(parameter)
This doesnt work. How / where can I calculate the variables and how can I use them in the input stage ?
*****
I read about Stage variables, but they are displayed on the output side of the transform stage, so I dont understand how they actually work. I dont know if this is the key to my problem.
Im actually doubt that, because in the transform, Im already past the input stage, and there is no constraint option on the input side of the transform.
Dirk Moolman
Database Administrator
Reach Technologies
South Africa
www.thefuelgroup.co.za
"No pessimist ever discovered the secret of the stars, or sailed to an uncharted land, or opened a new doorway for the human spirit." -Helen Keller
==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
Thanks Tony, I am now trying the parameter approach. This should work - I will test it soon.
Dirk
-----Original Message-----
From: Tony Curcio [mailto:tonycurcio@yahoo.com]
Sent: Thursday, October 25, 2001 3:41 PM
To: datastage-users@oliver.com
Subject: Re: Using variables
Are you defining the variables once per job (ie. they get set at the outset of the job and remain persistant)? If so, you will want to pass these in as parameters (go to the Edit / Job Properties menu and click on the parameters tab). Once you create the parameter, you can refer to it in your sql by surrounding the parameter with the "#" sign (as in "#variable1#"). If you are saying that you want these values to change as the job is running, I will need more detail about what you are trying to accomplish.
Dirk Moolman wrote: Hi, I have to use variables in one of my INPUT stages. Data will be selected according to 2 variables
select *
from product
where company = variable_1
and client = variable_2
..... but variable_1 & variable_2 still has to get values, so I tried (user defined sql, using a user defined routine)
select *
from product
where company = CalculateCompany(parameter)
and client = CalculateClient(parameter)
This doesnt work. How / where can I calculate the variables and how can I use them in the input stage ?
*****
I read about Stage variables, but they are displayed on the output side of the transform stage, so I dont understand how they actually work. I dont know if this is the key to my problem.
Im actually doubt that, because in the transform, Im already past the input stage, and there is no constraint option on the input side of the transform.
Dirk Moolman
Database Administrator
Reach Technologies
South Africa
www.thefuelgroup.co.za
"No pessimist ever discovered the secret of the stars, or sailed to an uncharted land, or opened a new doorway for the human spirit." -Helen Keller
==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
---------------------------------
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
Dirk
-----Original Message-----
From: Tony Curcio [mailto:tonycurcio@yahoo.com]
Sent: Thursday, October 25, 2001 3:41 PM
To: datastage-users@oliver.com
Subject: Re: Using variables
Are you defining the variables once per job (ie. they get set at the outset of the job and remain persistant)? If so, you will want to pass these in as parameters (go to the Edit / Job Properties menu and click on the parameters tab). Once you create the parameter, you can refer to it in your sql by surrounding the parameter with the "#" sign (as in "#variable1#"). If you are saying that you want these values to change as the job is running, I will need more detail about what you are trying to accomplish.
Dirk Moolman wrote: Hi, I have to use variables in one of my INPUT stages. Data will be selected according to 2 variables
select *
from product
where company = variable_1
and client = variable_2
..... but variable_1 & variable_2 still has to get values, so I tried (user defined sql, using a user defined routine)
select *
from product
where company = CalculateCompany(parameter)
and client = CalculateClient(parameter)
This doesnt work. How / where can I calculate the variables and how can I use them in the input stage ?
*****
I read about Stage variables, but they are displayed on the output side of the transform stage, so I dont understand how they actually work. I dont know if this is the key to my problem.
Im actually doubt that, because in the transform, Im already past the input stage, and there is no constraint option on the input side of the transform.
Dirk Moolman
Database Administrator
Reach Technologies
South Africa
www.thefuelgroup.co.za
"No pessimist ever discovered the secret of the stars, or sailed to an uncharted land, or opened a new doorway for the human spirit." -Helen Keller
==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================
---------------------------------
Do You Yahoo!?
Make a great connection at Yahoo! Personals.