Originally posted by: SystemAdmin

I've been playing with this long enough that I finally decided to look for help and look like a newbie. How do I nest the SORTUP function to alphabetically sort my records based on a field in the data?

INPUT
123,def
123,abc
...

OUTPUT
123,abc,
123,def

The following would work if it were the first field, but I need to SORT on the second field.

=F_Record(SORTUP(Record:Message:File_In)

Thanks for the help!
Bill
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Hello! The TX SORT function sorts a record from the first position.

To get around this you need to reformat your record on another output card then sort that file and use that in subsequent processing, or after it is sorted, reformat it back to the original format on still another output card if you need to keep the original format.

Not sure of the size of the file but if you run your map on Unix or Linux it would be a lot faster, for very large files, here it slows a lot with files greater than 100 MB, to use the shell adapter on an output card, call a shell script which does a system sort on the file, specify your second field start position and length, sort the file, then read it back into your map or create another map and read as an input file. The sort runs so much faster in native Unix for large files.

Have submitted a request previously to have the SORT function modified in a future release to allow sort positions to be specified without reformatting the record but have not heard anything yet.

Hope this helps.

Thanks!
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

It is possible to perform multiple key sorts using DataStage TX so there should be no reason you can't sort on column two of your records. Multiple key sorts were discussed here on the forum in March of 2002. The solution I suggested at that time requires you define Fields (s) as a component of Records (s). The map takes the input file:
04,Abe
07,Dick
01,Abe
03,Carol
02,Carol
10,Dick
10,Abe
10,Carol
07,Abe

and produces the output file:
01,Abe

02,Carol

03,Carol
04,Abe
07,Abe
07,Dick
10,Abe
10,Carol
10,Dick

Now why those blank lines occur I don't recall but I'm sure they can be eliminated.

The nested functional map rules are:
=SortOnField1(SORTUP(UNIQUE(Field[1]:Record:Input)), Input)
=SortOnField2(SORTUP(UNIQUE(Field[2]:Record:Input)), ID, Input)
=EXTRACT(Record:Input, Field[2]:Record:Report:Input = Name & Field[1]:Record:Input = ID)

For your requirement, I believe you would only need the second FMap and the third FMap without the Second condition.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange