Sorting behavior

bbrown
bbrown Member Posts: 3,268
edited 2008-02-01 in NAV/Navision Classic Client
When sorting text columns the native database will sort letters then numbers. The SQL version reverses this sort or places numbers before letters.

When using the SQL version I've been able to get the native behavior by using Navision's temporary tables. The are created on the client and behave the same in either database.

My current problem is I have a native database and need to sort a text column like SQL does. Any thought?
There are no bugs - only undocumented features.

Answers

  • mstallmann
    mstallmann Member Posts: 138
    I believe the difference is with CODE fields, not text fields. If so, then for the code field, use a text field. i.e. Populate the text field with the same data as the code field. This is how SQL sorts, as it is a character field in SQL... Obviously, if this is quite a bit of data, this is a bad idea...
    www.Oztera.com
  • bbrown
    bbrown Member Posts: 3,268
    mstallmann wrote:
    I believe the difference is with CODE fields, not text fields. If so, then for the code field, use a text field. i.e. Populate the text field with the same data as the code field. This is how SQL sorts, as it is a character field in SQL... Obviously, if this is quite a bit of data, this is a bad idea...

    I have used the approach you mention to get around the Code vs. Varchar behavior.

    This issue is with Text fields. Create a table where the primary key is a text field. Try it in both native and SQL.

    Navision (Native) gives them:

    WHLASH
    WH0003
    WH9205



    Navision SQL gives: (What they want)

    WH0003
    WH9205
    WHLASH
    There are no bugs - only undocumented features.
  • mstallmann
    mstallmann Member Posts: 138
    I am probably missing something, as your example shows a pattern, but other values might produce other results, but what about sorting descending (DESC)?
    www.Oztera.com
  • bbrown
    bbrown Member Posts: 3,268
    mstallmann wrote:
    I am probably missing something, as your example shows a pattern, but other values might produce other results, but what about sorting descending (DESC)?

    That would result in: (not what is needed)

    Navision (Native) gives them:

    WH9205
    WH0003
    WHLASH


    Navision SQL gives: (What they want)

    WHLASH
    WH9205
    WH0003


    Thanks for the responses. The search continues....
    There are no bugs - only undocumented features.
  • bbrown
    bbrown Member Posts: 3,268
    Used ADO to hold records in memory then sorted and processed. Solution came from one of our customers.
    There are no bugs - only undocumented features.

Categories