Limiting the records to load into sequential file

monaz · Post by **monaz** » Fri Jul 10, 2009 12:11 am

Hi All,

I have a requriement in which i need to load only 100,000 records into final output file .

My input is the table where we expect around 200,000 records per day. but the requirement is that i need to load only 100,000 records into the output file and the remaining data on the next the same job will be executed i have to load the remaining 100,000 records into the file plus the daily transaction records which are expected to be around 10 records per day.

Please let me know if the requirement i have not clear.

Please suggest..

ArndW · Post by **ArndW** » Fri Jul 10, 2009 12:17 am

A constraint of "@OUTROWNUM<100001' on the last transform would do the trick.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 10, 2009 1:30 am

The requirement is decidedly unclear.

Which 100,000 records do you load into the output file? The first 100,000 for the day, the last 100,000 for the day, or is it based on some other criterion?

What is supposed to become of source records not loaded into target on any particular day? Will the same job be executed again the same day to process these?

Is, perhaps, your requirement to split the source into 100,000-row subsets?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jul 10, 2009 2:07 am

Maybe you can load all records into a temporary table with a serial number and constraint the load into your target table.

But why do anybody have such a requirement !!?? Maybe they want to split their daily workload of checking the data !!??

What is the expected action if the following day's record(s) update old record(s) which are not yet loaded due to this constraint ?

monaz · Post by **monaz** » Fri Jul 10, 2009 3:28 am

ArndW wrote:A constraint of "@OUTROWNUM<100001' on the last transform would do the trick. ...

Thanks for all for there quick response.

My requirement is that they will run the job on daily basis , so based on the date feild if the date feild is having a value of today then i need to pass those records into the output file .

The ouput file should contain only 100,000 records per day.

For an instance if i have 200,000 records for the day then the user needs to view only 100,000 records for the day and the reamining records it should be on the next day when the same job is executed.

So on the next day the file will contain today's latest accounts as well yesterday's 100,000 record in the file.

Please suggest..

monaz · Post by **monaz** » Fri Jul 10, 2009 3:35 am

ray.wurlod wrote:The requirement is decidedly unclear.

Which 100,000 records do you load into the output file? The first 100,000 for the day, the last 100,000 for the day, or is it based on some other criterion?
...

Sorry for not giving an clear requirement

Let me make more clear and precise

1. The job executes on daily basis
In the input we have few transactions made on daily basis(Estimated around 10 records per day) as well the historical dates record.

Output expected is that
The file should contain today record(that is daily basis record around 10) and apart from that they need 100,000 records in the same file to display on the same day.

And when the next day the same job gets executed the file should contain the daily transaction records and remaining 100,000 records of historical data.

The same way job should continue

Note:- If the day one display's 100,000 historical records for that day . The next day should not have same 100,00 records of day one it should have other 100,000 historical records.

please help me on this

jcthornton · Post by **jcthornton** » Fri Jul 10, 2009 8:18 am

As long as the data is sorted coming into your final transformer such that the 10 current records are first, the previously suggested use of @OUTROWNUM < 100001 as a constraint would be the way to go.

Then you can add a second link off the same transformer with a constraint of @OUTROWNUM > 100000 (or @OUTROWNUM >= 100001 if you prefer) that goes to a new file to be used as the base for the next run's records.

That is a very strange requirement you have. Anything you can share on why the limit of 100k records and why the historical records cannot simply be loaded in an initial load type process?

monaz · Post by **monaz** » Sun Jul 12, 2009 11:37 pm

jcthornton wrote:As long as the data is sorted coming into your final transformer such that the 10 current records are first, the previously suggested use of @OUTROWNUM < 100001 as a constraint would be the way to go.

Then you can add a second link off the same transformer with a constraint of @OUTROWNUM > 100000 (or @OUTROWNUM >= 100001 if you prefer) that goes to a new file to be used as the base for the next run's records.

That is a very strange requirement you have. Anything you can share on why the limit of 100k records and why the historical records cannot simply be loaded in an initial load type process?

Thanks,

Since the output of the file is Report online report so they want to display 10,000 records on daily basis.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 13, 2009 12:12 am

Now it's even more confusing. Is it 10,000 or 100,000 ?