Reverse the records in DataStage

fahad · Post by **fahad** » Sat Sep 25, 2004 12:58 am

dear all,
i have set of records and i want to reverse them.. for example,

01 John IBM
02 George MSN
03 Peter Oracle

required to be

03 Peter Oracle
02 George MSN
01 John IBM

is that possible in DataStage???

thank you all and i will appreciate any reply

andru · Post by **andru** » Sat Sep 25, 2004 2:21 am

Is ur input a file or a ODBC/DRS stage?? If the data is fetched using a SQL query in the ODBC/DRS stage then use ORDER BY DESC to get the data in the reverse order.
If your inpt is a file then I doubt if we can do it in DS.

fahad · Post by **fahad** » Sat Sep 25, 2004 2:42 am

thank you for the reply.. however,
the source table is not sorted in asc or desc base order. my main problem is the duplicates in the source table and using hash file stage i can remove the duplicates by keeping the last record while i need the first one.. so i thought of reversing the records order by some mechanism to get through this problem..

your post opened my mind to new techniques i might use.. thanks a lot

chulett · Post by **chulett** » Sat Sep 25, 2004 4:16 am

If you do end up using a hash file to remove the duplicates in your file, you could store the record number (@INROWNUM) along with your data. Then, by accessing the hash file with a UV stage, you could pull them back out in reverse order.

vmcburney · Post by **vmcburney** » Sun Sep 26, 2004 5:27 pm

You could send it through a sort stage sorting in ascending order. With this sorted data you can remove duplicates with an aggregation stage or a hash file or stage variables. See the deduplicing FAQ for details of each.

With a hash file you will find records overwriting each other leaving you with the last record for each combination.

With the aggregation stage you can use the "Last" aggregate function on non group by fields. Make sure you populate the Sort information on your input columns to speed up the performance of the aggregation stage.

Stage variables will probably be the fastest method but also the trickiest to code.

badri · Post by **badri** » Mon Sep 27, 2004 3:36 am

Hi,

You can avoid duplicate records by following the below steps

Sort the records and in the transformer call a routine that contains basic code as below

DupFlag="N";
if trim(prNo)=trim(Field1) then
DupFlag="Y";
end
prNo= Field1;

Ans=DupFlag;

Filter out DupFlag = "Y" in the transformer stage

-

gh_amitava · Post by **gh_amitava** » Mon Sep 27, 2004 5:27 pm

Hi,

You can do it very easily. Put your source of data first then put a sequence generator stage, generate sequence from 1 for each of the records. Then sort the records in decending order. You will get the desire result.

Thanks
Amitava

ray.wurlod · Post by **ray.wurlod** » Mon Sep 27, 2004 9:39 pm

Version 7.1 server jobs (this IS the server-job forum) don't have the sequence generator stage to which you refer.

chulett · Post by **chulett** » Mon Sep 27, 2004 10:07 pm

Exactly. The OP specified "Server" as the job type, so it doesn't really help to give PX specific answers. Besides, there's a different forum for Parallel jobs.