Finding Max and Min on varchar column

[sriram_bcl]

Can any one suggest how to find Maximum and Minimum on varchar column in Datastage 8.1

[ray.wurlod]

Welcome aboard. Use an Aggregator stage.

[sriram_bcl]

Thanks for the reply. I tried all the possibilities with Agg. We have incoming data contains like 'abcdefg' etc. we are facing issue in finding min and max on string. In server job using aggregator we are able to do. in PX i am not able to do it. Plz suggest

[jerome_rajan]

Please post the expected and the actual outputs.

[sriram_bcl]

My iniput column contains
112A
112B
112C

My output should be Max(Column) i.e 112C

[ArndW]

I just tried it and it would seem that the MAX() function of the aggregator stage is limited to numeric data in Parallel jobs.

Along similar lines to what Ray mentioned, use one stream for a sort and a remove duplicates (first or last, depending upon whether you want a min or a max value) to get the highest or lowest value of that field.

[sriram_bcl]

This problem persist in existing project. Combination of Sort and Remove duplicate did not work. We tried long time back but did not work.

[ArndW]

That's quite odd, I did a test job earlier to see about the aggregator and had a functioning max() working using a sort and remove duplicates within 5 minutes. Perhaps you might want to revist your approach, and if you wish assistance here you might wish to post what you did and what the problem was.

[venkateshrupineni]

u can do using only sort stage and sort the data in desc select first record using transformer stage (read transformer stage in sequential mode) and write constraint is @inrownum=1 if you need only one record.

[ray.wurlod]

There is a Preserve Type property (or some such name) in the Aggregator stage that allows it to work with string data, among other things.