How to split into n number of records from one record

[deva]

Hi,

I have one table the formate is

idno, amount, begin_dt, end_dt, no.Premiums

1 4000 01-06-2005 31-06-2005 4

if i have no.Premiums are 4 I need o/p with 4 records

from the above record , into target I have get 4 records based on the no of Premiums.

required o/p is
idno,instal_amt,due_date

1 1000 03-06-2005
1 1000 06-06-2005
1 1000 09-06-2005
1 1000 12-06-2005

Thanks in advance

[DeepakCorning]

The only thing that I can think of is to create a base job , and put it in a loop for the number Premium. Everytime when its called do a select like

Select idno, amount / noPremiumns, begin_dt + ___ from table.

this may not be an ideal solution as it will be a heck of performance hogger. But it will do wht u want to do. I will try to come up with something more effective.

[gateleys]

I would NOT run the job in a loop. Rather, run the job only once, however, with a call to a routine that writes as many rows as there are Premiums.

[ray.wurlod]

Definitely a routine. What are the rules for establishing the dates and premium amount in the individual output records? It would be a Very Good Idea to document these business rules before designing anything, and getting agreement (sign off) that the specification is correct.

[deva]

Definitely a routine. What are the rules for establishing the dates and premium amount in the individual output records? It would be a Very Good Idea to document these business rules before designin ...

we have to split the dates and premium amount based on the no of premiums.

Can any one please provide the routine for this.

[ray.wurlod]

Can any one please provide the routine for this.

That's the kind of thing consultants charge money to do.

I still don't believe that "we have to split the dates and premium amount based on the no of premiums" is a sufficient specification. If you had the adverb "equally" in there somewhere it would help (if true). But are there extra rules, like every date has to be a Friday, that you have not revealed?

[SHARAD123]

Hi,
I suggest u to pass the premium as a parameter to a stored proc through OCI stage. Then write your business logic based on the parameter number. i hope this meets the requirement

[sachin1]

i agree with gateleys, you need to create a routine which will take necessary input parameter and file name, file needs to be in append mode(opened and closed for each record processing).

Using stage variable you create a long string with a new line character as separator for each record, then try to write this long string with transformation routine.