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
How to split into n number of records from one record
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Re: How to split into n number of records from one record
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.
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.
Thanks
Deepak Patil
Convince Them Confuse Them .. What's the difference?
Deepak Patil
Convince Them Confuse Them .. What's the difference?
Re: How to split into n number of records from one record
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.
gateleys
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
we have to split the dates and premium amount based on the no of premiums.ray.wurlod wrote: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 ...
Can any one please provide the routine for this.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That's the kind of thing consultants charge money to do.deva wrote:Can any one please provide the routine for this.
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.