Sorting database output uppercase alphas or lowercase alphas first

Sorting database output uppercase alphas or lowercase alphas first

I am using an advanced query with an order by clause for one column. The Oracle database returns records sorted
numbers first
then lower case alphas
then upper case alphas.
That is what I see in Toad.

Yet in Outsystems I see the sorting
numbers first
then upper case alphas
then lower case alphas

We had the same statement run in Platform and it would return the same sorted output as the database but in it seems to have changed.

Is it possible to adjust the sorting rules somewhere in addition to the order by clause we are already using?
Thanks, Daniela
Hi Daniela,

you can change the NLS settings temporarily...

For instance, you can run an advanced query with "ALTER SESSION SET NLS_SORT='BINARY'" in the beginning of an action and then you rollback to the previous setting before the action's end.

Thanks for your response Joao.

This would change a database setting though, wouldn't it? But I see the correctly sorted output from teh database already. It seems like Outsytems is changing the sort order somehow. Would changing the NLS_SORT paramater help me then?

You have to see what is the type of sort you want and you only change it for a brief moment.

Like I've said, you can alter it in the beginning of an action and before ending you revert it.

I would like to try this.

Wher can I findd teh Session_AlterNLS and Session_RevertNLS Actions?

Can you tell that I am not a super expert of Outsystems? :-)

Session_AlterNLS is just an action you create with an advanced query that executes "ALTER SESSION SET NLS_SORT='BINARY'" 

To revert, the advanced query may have "ALTER SESSION SET NLS_SORT='BINARY_AI'".

Just use the appropriate type of sort for your case.

Ah, ok. Thanks. :-)
The NLS_SORT is set to Binary n teh database already so this won't make a difference, or will it?