LastRowInGroup

Rob4732 · Post by **Rob4732** » Wed Mar 02, 2011 11:21 am

I have read several threads pertaining to the LastRowInGroup(), LastRow() and LastTwoInGroup() functions.

I want to include one of the above functions in the derivation of a staging var, but none seem to be there. Only see the standard functions(Date & Time, Mathematical, Null Handling, String, etc...)

There is probably a simple answer to this and I'm not seeing it.

Thanks

Robert

P.S. - I also don't see the system variable @ITERATION.

chulett · Post by **chulett** » Wed Mar 02, 2011 11:41 am

Since you didn't specify, let's get this out of the way - are you running the 8.5 release?

Rob4732 · Post by **Rob4732** » Wed Mar 02, 2011 1:34 pm

Yes, running 8.5

thx

ray.wurlod · Post by **ray.wurlod** » Wed Mar 02, 2011 3:49 pm

Are you seeing a Loop Condition area in your Transformer stage? If not, was this job upgraded/imported from an earlier version? If so, create a new job with a new instance of Transformer stage and let us know what you can find in that.

Rob4732 · Post by **Rob4732** » Wed Mar 02, 2011 3:59 pm

Hi Ray,

Yes, there is a loop condition in the transformer. Job was created in datastage 8.5.

I also created a new job in 8.5(in case something was corrupt in my original job), but see same problem in new job.

I have created a PMR with IBM.

Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Mar 02, 2011 4:09 pm

I suspect @ITERATION system variable is only visible if you're editing an expression with the Loop area.

Functions LastRow() and LastRowInGroup() are in the functions tree (even when editing stage variable or output link expressions) in the Last Row Handling branch of the functions tree.

There is no LastTwoInGroup() function unless you create your own.

I'm currently running on Red Hat Linux, and the above is what I have verified.

Rob4732 · Post by **Rob4732** » Wed Mar 02, 2011 4:17 pm

Thanks.

I currently don't have the 'Last Row Handling' branch in the function tree.

I also added the following to my 'loop condition':

@ITERATION <= 3

Note - I had to type in @ITERATION, as it is not available for selection.

When I validate the 'loop condition', I get the following message:

variable @ITERATION not defined.

Thanks for the input.

ray.wurlod · Post by **ray.wurlod** » Wed Mar 02, 2011 4:24 pm

In the Repository open \Stage Types\All\Processing\Transformer Stage - does it have a 2010 IBM copyright on the Creator page?

Rob4732 · Post by **Rob4732** » Wed Mar 02, 2011 4:32 pm

Below is text in the copyright box from the Creator tab of \Stage Types\All\Processing\Transformer:

Licensed Materials - Property of IBM, (C) Copyright IBM Corp. 1999, 2010 All Rights Reserved.

On a side note, we installed 8.5 about a month ago and this is the first time we have tried the looping functionality. Not sure if we missed something in the install.

Thx

ray.wurlod · Post by **ray.wurlod** » Wed Mar 02, 2011 4:35 pm

There's nothing special in the install. I'm now out of ideas, you seem to have the correct version of the stage (it has a Loop Condition area), so we'll wait to hear what IBM support have to say.

acx_dyates · Post by **acx_dyates** » Thu Mar 03, 2011 10:06 am

Rob4732 wrote:I have read several threads pertaining to the LastRowInGroup(), LastRow() and LastTwoInGroup() functions.

I want to include one of the above functions in the derivation of a staging var, but none seem to be there. Only see the standard functions(Date & Time, Mathematical, Null Handling, String, etc...)

There is probably a simple answer to this and I'm not seeing it.

Thanks

Robert

P.S. - I also don't see the system variable @ITERATION.

Do you have an old DSParams by chance (from an earlier version?)

acx_dyates · Post by **acx_dyates** » Thu Mar 03, 2011 10:44 am

The 8.5 DSParams should have entries like:

[parallelonly-system-variables]
@PARTITIONNUM
@NUMPARTITIONS
@ITERATION\O\!V

...and would also include the function entries that you mentioned uder the section below.

[parallelonly-functions]

Rob4732 · Post by **Rob4732** » Thu Mar 03, 2011 10:48 am

Thanks Everyone for the suggestions.

I was about to post the solution when I read acx_dyates post, which describes what IBM found to be the problem also. We replaced our 8.5 DSParams file with an 8.1 version(resulting in missing functions, sysvars and probably other things in our 8.5 environment). DSParam files are NOT plug and play between earlier versions of datastage and 8.5. Replacing our 8.1 version of DSParams back to the 8.5 version solved the problem. Of course we have to re-enter our 8.1 env var info and settings via admin again in 8.5.

Thx

Robert

ray.wurlod · Post by **ray.wurlod** » Thu Mar 03, 2011 3:18 pm

There is a Migration Assistant tool that, among other things, looks after merging modified DSParams files.

DSXchange

LastRowInGroup

LastRowInGroup

Re: LastRowInGroup

Re: LastRowInGroup