Finding the last record in a transformer
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 67
- Joined: Mon Dec 22, 2008 4:07 am
Finding the last record in a transformer
Is there any way to find the last record in a transformer?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can use the LastRecord() function. (I think that's what it's called.)
To use this you need to be running version 8.5.
To use this you need to be running version 8.5.
Last edited by ray.wurlod on Mon Aug 29, 2011 2:07 am, edited 1 time in total.
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.
@INROWNUM = 2 is not working in datastage 8.5
criteria:
data in the file: (has only 2 rows)
ABCDEFGHI
123456789
In the output I want it as :
00000ABCDEFGHI00000123456789
stage variables:
alphabet:@INROWNUM = 1
number:@INROWNUM = 2
The constraint in the transformer to print the second row is not working (@INROWNUM = 2).
Where will we find the partitions per enabled for a job?
data in the file: (has only 2 rows)
ABCDEFGHI
123456789
In the output I want it as :
00000ABCDEFGHI00000123456789
stage variables:
alphabet:@INROWNUM = 1
number:@INROWNUM = 2
The constraint in the transformer to print the second row is not working (@INROWNUM = 2).
Where will we find the partitions per enabled for a job?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: @INROWNUM = 2 is not working in datastage 8.5
Everywhere.Immaculin wrote: Where will we find the partitions per enabled for a job?
Parallel execution is enabled by default in the parallel Transformer stage.
Open up the stage properties. On the Advanced tab you will be able to change the execution mode to Sequential.
Do consider using a server job for this task. It will be finished while the parallel job is still thinking about starting.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can if you get yourself a premium membership. By doing so you will be helping to keep this site alive; premium membership is the main way that hosting and bandwidth costs are met.Immaculin wrote:I will not be able to view the premium content.
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.
Re: @INROWNUM = 2 is not working in datastage 8.5
Server job.Immaculin wrote:data in the file: (has only 2 rows)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
use this, it worked for me
For flat files:
First you need to know the rowcount for your file:
In the job properties use before job subroutine command execsh and type
"cat filename | wc -l > rowcount.txt"
Have a lookup in your job that looksup the file rowcount.txt and gets the rownum value. Generate rownum using @INROWNUM for the source records. Use the rownum as the key for both the source and lookup data. Set lookup condition to continue on condition not met and lookup failure. Add a transformer after the lookup and add condition where src.rownum=lkp.rownum and divert this record. This is your last record in the source.
For database sources your lookup needs to have "select count(1) from src_table" to find the table rowcount and repeat the same logic.
First you need to know the rowcount for your file:
In the job properties use before job subroutine command execsh and type
"cat filename | wc -l > rowcount.txt"
Have a lookup in your job that looksup the file rowcount.txt and gets the rownum value. Generate rownum using @INROWNUM for the source records. Use the rownum as the key for both the source and lookup data. Set lookup condition to continue on condition not met and lookup failure. Add a transformer after the lookup and add condition where src.rownum=lkp.rownum and divert this record. This is your last record in the source.
For database sources your lookup needs to have "select count(1) from src_table" to find the table rowcount and repeat the same logic.