SQL and sorting

Roelof
Roelof Member Posts: 377
edited 2005-04-12 in NAV/Navision Classic Client
Hi,

Probably this question has been asked many times, but here it is again: One of our clients is running Navision 4.00 in a SQL environment. We upgraded from 3.01 to 4.00. It seems to be that sorting orders are not quiet the same as in Native Navision. I.e 1..20 is in SQL 1,10,20,3..9. Is there any way to change that?

Thanks!
Roelof de Jong.
Roelof de Jonghttp://www.wye.com

Comments

  • Savatage
    Savatage Member Posts: 7,142
    http://www.mibuso.com/forum/viewtopic.p ... t=sql+sort
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • mestre
    mestre Member Posts: 24
    I don't believe that you should read the post in the link.

    You must search for one property somewhat SQL data Type, I'm sorry but I don't have the Navision in front of me.
    Cumps,
    Mestre
  • Roelof
    Roelof Member Posts: 377
    Well, if I looked at the links, I was afraid of that. I know that you can solve the problem by using leading zeros, but that's not a solution. It would mean that we have to do this 'trick' in many many tables and to rename every single record. An other solution I read would be an additional field and fill that with leading zeros and add that to the sorting keys. Not an easy solution and very very time consuming.

    Sure there is no other solution?

    Roelof de Jong.
    Roelof de Jonghttp://www.wye.com
  • kine
    kine Member Posts: 12,562
    I think no. Your numbering does not follow basic recommendations... if you are using only numbers, you can retype the field (but you need to somehow transfer the data out and in) and change all references... but it is still big job... I recommend you to change the numbering to follow the rules for numbering...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Savatage
    Savatage Member Posts: 7,142
    mestre wrote:
    I don't believe that you should read the post in the link.

    :?:
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • PaddyMullaney
    PaddyMullaney Member Posts: 59
    The SQL Data Type mestre was talking about is SQL Variant.

    I could not get it to work properly, and when I spoke to MBS they mentioned it might not always be that great.

    If you re-number you are best to use a character rather than trailing zeros to create the number.
    e.g
    a001
    a021

    instead of

    0001
    0021


    Also the only place in Navision this is a major issues as opposed to just a display issue is when you use a Chart Of Accounts as the Sums in that are based on referenetial rather than absolute references. If you re-number within Navision, Navision can update all related links for you. I have not tested it anywhere else, so you would have to tread a little carefully there. It can take some time, especially in SQL.

Categories