Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Unanswered: Wrong sorting results

    Have a java based application displaying query results from an oracle database. Sorting is done using "ORDER BY (CASE WHEN SUBSTR(field_name, 1, 1) BETWEEN '0' AND '9' THEN 0 ELSE 1 END) || NLSSORT(field_name, 'NLS_SORT = GERMAN_AI')".
    Sorting is corect on development system, but on productive system order gets mixed up, i.e. first letter "c, a, b, f, d, e, i, g, h, l, j, k..."
    Also, using an sql client, query returns corect order for both database layers. Seems that wrong ordering is returned only from java application on productive system.
    Any ideas?

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by cignat View Post
    "ORDER BY (CASE WHEN SUBSTR(field_name, 1, 1) BETWEEN '0' AND '9' THEN 0 ELSE 1 END)
    Sorting is corect on development system, but on productive system order gets mixed up, i.e. first letter "c, a, b, f, d, e, i, g, h, l, j, k..."
    If you want to sort by first letter, then use ORDEB BY substr( field_name, 1, 1).

  3. #3
    Join Date
    Feb 2012
    Posts
    3
    Quote Originally Posted by kordirko View Post
    If you want to sort by first letter, then use ORDEB BY substr( field_name, 1, 1).
    ORDER BY clause used is for german sorting of whole word but putting digits as first letter on top. Thanks anyway.

  4. #4
    Join Date
    Feb 2012
    Posts
    3
    However we got additional request to treat any consecutive digits at beginning of word as whole number (i.e. '20xxx' order before '100xxx' because 20 < 100), and we have tried ORDER BY TO_NUMBER(REGEXP_SUBSTR(field_name, '^[0-9]*')), NLSSORT(field_name, 'NLS_SORT = GERMAN_AI'). This seems to be working corectly on both layers. For sure this new approach could have been used on old requirements also.

    So problem solved, maybe this would help. Cannot explain it though, would be interesting if anyone can.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •