IBM i Global

Hi,

I have few records in some file let's say f1.

it has multiple fields let's say fld1,fld2,fld3..fldn.

and let's say fld2 here is having sequence number (4 p 0 ) '0001' ,'0002' ...etc.

what i want is to swap the sequence numbers of these 2 records like '0002' to '0001' and '0001' to '0002' i did it like below AQL query:-

"UPDATE f1 set fld2 = CASE fld2

WHEN '0001' THEN '0002'

WHEN '0002' THEN '0001'

END

WHERE fld2 IN('0001' ,'0002')

so this SQL did what i wanted but not completely as i wanted the record with fld2 as '0001' to appear as my first record and '0002' as my second record but currently

it's showing as usual with fld2's value as '0001' as 2nd record and fld2's value as '0002' as 1st record so i want it to show here in reverse way (like as  fld2's value as '0001' value as 1st record and '0002' value of fld2 as 2nd record)

So how can we achieve it?

Thanks much..

Dear jerry

The solution to your issue is to use  SELECT..... FROM F1 ORDER BY FLD2. 

But if you want the physical sequence of the records in the file to be sorted as you asked about, you need to first create an index/keyed logical file over FLD2 and then run RGZPFM on F1 with the parameter KEYFILE(<index/keyed LF name>).  This method can be cumbersome, if not quite impractical, in real situations for a large file. 

------------------------------
Satid S
------------------------------