Sequential File

[Snowwwmannn]

Hi All,

I am new to Data Stage. I want to know whether it is possible if I want to extract data from 10 different records from the database and after transforming it , load into the same sequential file and How?

Say for eg. for one employee.. we have 10 different records where employees personal and job information reside and I want to extract that data and put it into fixed length sequential file.. in a way that one row for one employee which contains the most effective employees data...

Thanks & Regards,

Ralph

[chulett]

Welcome. :D

The sequential file is the easy part. You'll need to expand on your requirements, however. 'One row for one employee contains the most effective employee data' means what exactly? Of the 10 records, you need to pick only one? Or you need to combine information from the 10 records, picking and choosing fields to combine into a single record? Something else entirely?

The more specific you are, the more specific we can be.

[rafik2k]

What do you want? Be more specific.

Do you want latest information based on date or flag for a employee or the information chulett mentined in his reply?

[ray.wurlod]

Take a look at QualityStage. Your requirement is precisely what the "Survive" stage does - consolidates groups of candidate duplicates into a single record containing data from all candidates (where appropriate) so as to generate the "best of breed" record - the one containing the maximum amount of information. Matching is probabilistic rather than deterministic, and you determine the match rules and thresholds as well as the survivorship rules.

[Snowwwmannn]

Hi Craig,

Thanks for your response. Well I want to combine information from the 10 records, picking and choosing fields to combine and load into a single sequential file.

Thanks & Regards,

Ralph

[quote="chulett"]Welcome. :D

The sequential file is the easy part. You'll need to expand on your requirements, however. 'One row for one employee contains the most effective employee data' means what exactly? Of the 10 records, you need to pick only one? Or you need to combine information from the 10 records, picking and choosing fields to combine into a single record? Something else entirely?

The more specific you are, the more specific we can be. :wink:[/quote]

[chulett]

It would help if you didn't just repeat my own words back but instead described in detail the process you need to follow. Forget about DataStage, document what you need to do for this regardless of the tool being used to implement the logic.

Then people can help with the DataStage part.

[Snowwwmannn]

Hi Craig,

Thanks for you response. Well i want to combine information from 10 different records(tables) , picking and choosing fields to combine into a single record and load it into sequential file.

Regards,

Ralph


[quote="chulett"]Welcome. :D

The sequential file is the easy part. You'll need to expand on your requirements, however. 'One row for one employee contains the most effective employee data' means what exactly? Of the 10 records, you need to pick only one? Or you need to combine information from the 10 records, picking and choosing fields to combine into a single record? Something else entirely?

The more specific you are, the more specific we can be. :wink:[/quote]

[chulett]

It would help if you didn't just repeat my own words back but instead described in detail the process you need to follow. Forget about DataStage, document what you need to do for this regardless of the tool being used to implement the logic.

Then people can help with the DataStage part.

[Snowwwmannn]

Ok.... see the requirement is that we need to migrate employees data from one database to another...for one we are using Oracle apps and for the second one we are using PeopleSoft. so we want to extract employees data from first database and than do some transformation on the data and convert it into a sequential file but we dont want seperate files for seperate tables... we want one single file containing all the employee tables data. This way we can use single process to load data at the peoplesoft end.

I hope this make sense.

Ralph

[quote="chulett"]It would help if you didn't just repeat my own words back but instead described in detail the process you need to follow. Forget about DataStage, document what you need to do for this [i]regardless of the tool[/i] being used to implement the logic.

Then people can help with the DataStage part.[/quote]

[sia_999]

You can achieved this by adding 10 lookups for main employee table and collect the data and create one sequential file.

[Nisusmage]

Snowman, I agree with chulett.. you are not being very specific. *thought* drawing water from a stone *thought*.

I have 2 things I thing you can try:

Write yourself a very nice join to join to the same table a number of time and convert to rows to column data using some nice "case when isnull()'s" to force the data to one line and use a group by all the columns or simply distinct.
OR
Temp table and update the records as needed and pull from that temp table. If it's a once off migrating, I suggest doing this.

SQL can be very versitile just play around.

I've found the Pivot Stage, and it's seems to be your answer but I'm also quite new to DS and it really depends on your data schema. You'd have to play around here, I really haven't used this stage yet.

[ray.wurlod]

Have you installed the PACKs for Oracle Applications and PeopleSoft?