1. Gzim Ramizi
  2. PowerBuilder
  3. Wednesday, 22 May 2019 17:17 PM UTC

Hi all,

we are evaluating some ideas to change our "default"-sort order of string columns on the database (oracle 12g), because of some special character(sets). Until now, we supported just usual ansi/german letters, but since database and powerbuilder supports unicode, we would like to accept also other letters with special characters (e.g.: ã,è, ë, ...). One idea is to create a mapping-function (which converts special characters in some ansi-equivalent-characters, ä=>ae, ë=>e) and then use that utility-function in our colums... An other idea is to change the "nls_sort" of our Oracle Database, but then we would have different sort order on database compared to the client (datawindow)... Do you know a way how to change/manipulate the sort order of a datawindow? Or maybe you could exchange some ideas with me which you already used for similar issues...

As far as I see, datawindow sort order is based on the order of the character set (unicode or whatever) which is currently used.

Thank you in advance.

 

Kind regards.

Accepted Answer
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 22 May 2019 17:27 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Gzim;

   Yes, Appeon PB's sortation is based on the Unicode the UTF-16LE character set.

Regards ... Chris

Comment
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Wednesday, 22 May 2019 22:29 PM UTC
  2. PowerBuilder
  3. # 1

Hi Gzim,

Regional settings influence sorting order in the PB client app. It can also influence sorting order in the database.

Real life example I experienced >>>

  • Corporate policy, server settings, and all but one user machine said regional settings = US English.
    • A sorts before Z
    • AA sorts before ZZ
    • AAA sorts before ZZZ
  • Single user had regional settings = Danish.
    • A sorts before Z 
    • AA sorts *AFTER* ZZ
    • AAA sorts *AFTER* ZZZ

Background to understand unexpected sorting: In 1948, Å was introduced in Danish to replace the more verbose AA. However, original AA is still legal and still represents Å - so AA sorts after ZZ.

Other languages may also have unexpected sorting sequences that you need to be aware of.

HTH /Michael

Comment
  1. Michael Kramer
  2. Monday, 27 May 2019 19:58 PM UTC
I would warn against writing your own collation algorithm because it is complex and time-consuming to get right. Instead, search for a library or O/S API that knows how to collate.

Examples:

1) Spanish: NZ wedish: Z . However in Norwegian: Z guages character with and without accents/umlauts collate the same (like Olan's example). In others, they don't! Or they do, partially!

  1. Helpful
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Sunday, 26 May 2019 00:51 AM UTC
  2. PowerBuilder
  3. # 2

Frankly, it sounds like you want a definitive SORT process that is NOT the standard UNICODE UTF-16LE sort order.

If this is the case, see if a function exists by which special characters can be identified and "stripped" of their non-standard feature. Or write one.

Example:  stripping the special character from  è and ë leaves  e.
              Now you can sort and both è and ë will sort as an  e.

 

You can also set up a secondary set ofrules to specify which special characters sort in what sequence.

Example:  The special character for è is  '  and the special character for  ë  is the umlaut ¨

 

Olan

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.