I want to sort a field which in not an index key, for example:

select tfcmg101.refr
from tfcmg101
where tfcmg101.refr = :tfcmg101.refr
order by tfcmg101.refr

selectdo
endselect

Why the tfcmg101.refr is not sorted when i using this method? Is there any mistake in the select statement? Is is possible to sort a non index key field?

In your example you are using tfcmg101.refr as selected field, input for you equals value and sort field. The result will either be nothing found or just one record, in the last case sorting makes no sense

Start using the correct field for you SQL equals part, sorting statement should be ok, you can extend it by using ASC or DESC to sort the output ascending of descending



"select tfcmg101.refr
from tfcmg101
where tfcmg101.refr = :tfcmg101.refr ( << ???? )
order by tfcmg101.refr"


Have fun

JP

Yes, we can sort data on non index key field.

Here, I am assuming that by mistake you have written wrong where condition (as pointed by JP) in this thread example.


Is it report session?
If answer is “Yes”: It may be possible in report; you have defined sort mode on some other field.

tfcmg101.refr in my case is a compulsory field where the user have to key in customer contract number.

So, in this example all the contract number will store in a field call as ctmp. The script will be like this:

select tfcmg101.*
from tfcmg101
where tfcmg101.refr = :ctmp
order by tfcmg101.refr

selectdo
message("%s", ctmp)
endselect

The output for this case is:
B
A
C

From the script, it suppose to be something like this right?
A
B
C

I'm wondering why no sorting for this output. Is there any mistake for the script? Or can i use qss.sort() for this case? Any idea how to apply qss.sort for this case?

There is nothing to sort for SQL query on tfcmg101.refr field as you are fetching data for specific customer contract number.

select tfcmg101.*
from tfcmg101
where tfcmg101.refr = :ctmp
order by tfcmg101.refr

selectdo
message("%s", ctmp)
endselect


I believe that above piece of code should be executed from some other loop or query.

Either you join tfcmg101 select query with that upper level query.
Or
Sort upper level query and then call this tfcmg101 select query inside the selectdo.

Please upload exact script logic so someone can help you.

Thanks Manish, yes the upper level will solve everything.