hi Guys,
My requirement is something like reading a sequential file with header and trailer then loading data into a oracle DB with some business logic in b/w along with a date field which will be read from the header. Then using the loaded records (Not all the colmns) to create an outbound file. For this I need to specify header date in my SQL query to fetch only those data which where loaded. Is it possible to initialize a job parameter or Environment variable inside the transformer after reading the header? I think its not possible, as parameter initialization takes place before running the job and if you use DSSETPARMS in current job to initialize a parameter in the same job, it will give you error=-1(invalid job handle). Taking max date in SQL is not a good logic, as it would fetch previous day data, if current day load is zero.Can anyone suggest a way around.
initializing parameter, inside a transformer stage
Moderators: chulett, rschirm, roy
-
talk2shaanc
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
You can make this a user defined query and use the parameter in a where clause like
select
from
where UPDATE_TS > to_date('#StartDate#')
You can calculate this date in the job before this. You will need to store this date in a hash file or sequential file and read it back to feed it into this job. You may need to write a batch job to do this.
select
from
where UPDATE_TS > to_date('#StartDate#')
You can calculate this date in the job before this. You will need to store this date in a hash file or sequential file and read it back to feed it into this job. You may need to write a batch job to do this.
Mamu Kim
Hi,
if I understood you correctly
:
you can split the job into 2 parts 1 job setting its own user status to hold the date value and the other receiving it as a parameter.
wrap both in a sequence job.
IHTH,
if I understood you correctly
:you can split the job into 2 parts 1 job setting its own user status to hold the date value and the other receiving it as a parameter.
wrap both in a sequence job.
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
You didn't specify what database. One way I have used with Oracle OCI stage, is to dynamically create the Sql statement into a named flat file, then specify that file as the source of the query in the OCI stage.
ex)
Derive the date value into stage variable dateSvar. Write the query into flat file Date.sql, formatted with a single varchar(255) column, no quotes, no delimters, etc.:
"select foo from bar where goo_date = '": dateSvar :"'"
Note the single and double quotes enclosing the date literal.
Then reference file Date.sql in the OCI query that fetches the dataset you want.
A kludge, yes, but it offers quite bit of flexibility, plus the generated Sql statement is available for debugging, logging, etc.
Carter
ex)
Derive the date value into stage variable dateSvar. Write the query into flat file Date.sql, formatted with a single varchar(255) column, no quotes, no delimters, etc.:
"select foo from bar where goo_date = '": dateSvar :"'"
Note the single and double quotes enclosing the date literal.
Then reference file Date.sql in the OCI query that fetches the dataset you want.
A kludge, yes, but it offers quite bit of flexibility, plus the generated Sql statement is available for debugging, logging, etc.
Carter