DSXchange

Hi All,
I have a target table Name as
X_07A,X_07B,etc.....
here:
07 is year
A-Jan
B-FEB,
C-MAR & so on.
I pass a recorddate:20070506 i.e(06-may-2007)
by using this record date can I dynamically build the target table name??
Can i use DSGetParamInfo & if I can suggest how...
Eagerly awaiting a reply as this would be really useful...

Not in one job. You can only build the target table name dynamically by passing these values as job parameters, then constructing the table name from these, or by constructing the table name in the invoking job sequence.

ray.wurlod wrote:Not in one job. You can only build the target table name dynamically by passing these values as job parameters, then constructing the table name from these, or by constructing the table name in the i ...

I am an amateur in datastage.
Can you help me how can I do the same??

what i get from ur question is that u want to create table based on the i/p values...


What u have to do is to use some parameter say "ipdate" and pass it to target stage where you can have ur table creation query and ur query will look smthg like:

Create table #ipdate# <column name> <col type>

you have to use a batch job that will read input dates one by one and should have logic to map Jan to A, Feb to B & so on.. and then you can store the final mapping to 'ipdate' and pass it to the table creation job one by one...

47shailesh wrote:what i get from ur question is that u want to create table based on the i/p values...


What u have to do is to use some parameter say "ipdate" and pass it to target stage where you can have ur table creation query and ur query will look smthg like:

Create table #ipdate# <column name> <col type>

you have to use a batch job that will read input dates one by one and should have logic to map Jan to A, Feb to B & so on.. and then you can store the final mapping to 'ipdate' and pass it to the table creation job one by one...

What do i do with batch job??
How do I read the date 1 by 1??
Kindly help....

If you have all the dates stored in a file then use this code in batch

datefile=filename with fullpath

Openseq <datefile> to <tempfile>
ReadSeq Rec from <tempfile> else eof=1
Loop Until eof
ipdate=Field(Rec, "|", 1)

'call the job and pass ipdate as parameter to it'

Repeat
WEOFseq <tempfile>
Close <tempfile>

You don't "need" a batch job, though one certainly could be used.

You could read the file in a Server job, build your table name from the date and then run your other job once per record. Look at the UtilityRunJob routine in the 'sdk\Utility' category, it is what you would use in the transformer. Double-click on it and check the Long Description.

47shailesh wrote:If you have all the dates stored in a file then use this code in batch

datefile=filename with fullpath

Openseq <datefile> to <tempfile>
ReadSeq Rec from <tempfile> else eof=1
Loop Until eof
ipdate=Field(Rec, "|", 1)

'call the job and pass ipdate as parameter to it'

Repeat
WEOFseq <tempfile>
Close <tempfile>

Thanx foor the code.
I dont have any file which conatins date.
I am passing date as parameter.
Say Parameter name is RDATE(2007-05-06).
What i want to do is,taking this date I want to build a table X_07E because,
from 2007, I get 07 & since month is 05(May) i get E.
Now I want to store this X_07E as a parameter and use it to update the table X_07E.
Can u help me again......

Call the job from a sequence and pass the table name as parameter and pass this as parameter from the sequence :

'x_' : '2007-05-06'[3,2] : if RDATE[6,2] = '10' Then 'J' Else if RDATE[6,2] = '11' Then 'K' Else if RDATE[6,2] = '12' Then 'L' Else Ereplace(Convert("123456789", "ABCDEFGHI", RDATE[6,2]), '0','')


where RDATE is the date you want to pass to conver into table name and RDATE is in this format YYYY-MM-DD.

JoshGeorge wrote:Call the job from a sequence and pass the table name as parameter and pass this as parameter from the sequence :

'x_' : '2007-05-06'[3,2] : if RDATE[6,2] = '10' Then 'J' Else if RDATE[6,2] = '11' Then 'K' Else if RDATE[6,2] = '12' Then 'L' Else Ereplace(Convert("123456789", "ABCDEFGHI", RDATE[6,2]), '0','')


where RDATE is the date you want to pass to conver into table name and RDATE is in this format YYYY-MM-DD.

Thanx.
What I thought was:
Call a before Jiob Subroutine.
Here Pass parameter RDATE as 2007-06-05
Then this routine will overwrite the RDATE as R_00107F.
Is this possible??
I have written the routine which converts date in required format.
But I am not knowing how to overwrite it as parameter again....
Any ideas????

Once a job has started you can not change its parameter values. They must be changed before the job starts - that is, in the controlling job (sequence).