Page 1 of 1
Adding a Column to a Table
Posted: Tue May 02, 2006 12:41 am
by amit_dwh
I have a data file like :
name|age
drew|23
moore|45
I have another query returning a max value of some column,say max_value be column name and its max value be 1000.
Now i want a output file like
name|age|max_value
drew|23|1000
moore|45|1000
How can this be done with best efficient way in Datastage.
Amit
Posted: Tue May 02, 2006 12:45 am
by ray.wurlod
It's easy enough in DataStage - if the target table has the extra column, simply use the new metadata (table definition) an assign the constant 1000 to that column for all rows. Probably easiest is a Column Generator stage with a cycle beginning at 1000 with an increment of 0.
Adding a Column to the Table
Posted: Tue May 02, 2006 2:40 am
by amit_dwh
ray.wurlod wrote:It's easy enough in DataStage - if the target table has the extra column, simply use the new metadata (table definition) an assign the constant 1000 to that column for all rows. Probably easiest is a ...
The constant value of max_value column is determined at the time of job execution and is not pre defined.
So i cant declare tht in meta data definition of the table.
Have to figure out some other way around.
Amit
Posted: Tue May 02, 2006 2:47 am
by roy
Hi,
Simply replcae the constant with a lookup to get runtime value.
The max value gained from the lookup should be loaded to memory so you should have no problem.
IHTH,
Posted: Tue May 02, 2006 3:24 am
by amit_dwh
roy wrote:Hi,
Simply replcae the constant with a lookup to get runtime value.
The max value gained from the lookup should be loaded to memory so you should have no problem.
IHTH,
If i use lookup then there is no key values on which i can specify the lookup.
So i will get Nulls returned for 1 table in Lookup stage.
Can u explain me the exact flow which ur talking about.
Thanks
Posted: Tue May 02, 2006 5:57 am
by ray.wurlod
Use a job parameter to supply the initial value for the cycle.