DSXchange

When you call it from TOAD or any other sql tool within the instance, you dont need to prefix the stored proc. with the schema name. But within DataStage you do need to fully qualify the name by prefixing the user to the stored proc.

Can't the business go off of a key column in the record? If not then add a running number to your file pre-hand before processing.

Did you do exactly what I asked you to do? Sorting, order of stage variables etc. As I said, thats tested code. If it works for me, it will work for you. Go through my post again and do exactly as I said. Can't help you more then that.

My code is complete, as far as I know. It calculates both even and odd entries. Additions can be made to it. Its custom code and hence marked as 'Premium'. Premium members can view it.

Search for ways to clear the &PH& folder.

Also keep in mind, the coded methodology might not exist in the next version.

I think its dsjob -warn 0, right. Or just keep that number a huge huge number.

You dont need to use the RowProcCompareWithPreviousValue for more than one time, I think. Just use the code I gave you. It will give you the Rank as you want. Its tested code.

Three stage variables, PrevThree, PrevSales and cond . Their derivations will be cond | if @INROWNUM =1 then 1 else if (in.STATE:in.CITY:in.ZIP= PrevThree) AND (in.SALES <> PrevSales) then cond +1 else 1 PrevThree | in.STATE:in.CITY:in.ZIP PrevSales | in.SALES The stage variables sho...

Thats by default. That number specifies a block of memory to hold the data. One block for writing to and the other is for reading. So if you put 128, that means 256 is specified. So I would say increase that. Let it be a multiple of the complete record size + a few bytes.

Did you try increasing the buffer size? How huge is your record?

Ok, this going in some other direction. I'll re-phrase my reply to Craig, "Me No Script"

Yes, sure

where x is the length of the date part.

You can also put TRUNC() in the sql select. I am doing the exact same thing in a few tables at my site.

It doesnt matter. The rule is
For subsequent rows, if the first three columns are same and the fifth column is different, only then increment, else 1.
DId you try coding as I suggested.

Well you will have to do a column by column check then. Compare column 1, if they are same, check scond column, if same check third, if same, check the 5th column. Now if the 5th column is same, refresh the rank to 1, if its different, increment the rank. If any of the first three columns are differ...