SQL vs. Native-DB: Different behavior when setting filters?

ta5
ta5 Member Posts: 1,164
edited 2006-09-26 in NAV/Navision Classic Client
Hi

I have a strange behavior here.
Table xy with 1 Field, Code 10.
Values in this field: 10,12,33,310,320

When I run the table and set a filter "30..39", I have the ffw difference:

1) Native DB, 4.02 Client
Record: 33

2) SQL 200 DB, 4.02 Client
Records: 310,320,33

Please note also the Sorting of the SQL-Result.

What's the reason for this? I have troubles with this behavior in situations like VAT preview, where records are defined as totals. ](*,)

Thanks
Thomas

Comments

  • Phenno
    Phenno Member Posts: 630
    Native DB and SQL DB looks different at type "Code" of the field and you can find a lot about that here, just search for "sorting, code".

    bottom line is:
    In native, Code field is sorted as integer and in MS SQL as text, which means, if you have
    10,12,33,310,320

    IN SQL will be sorted as
    10
    12
    310
    320
    33

    But in native it will be sorted like this
    10
    12
    33
    310
    320

    Because of that, filters that you make on this field respects that sorting.
  • ta5
    ta5 Member Posts: 1,164
    Hi Phenno

    Thanks for the info.
    This would mean, the ffw. Line would only work if "Row No." all have the same length (0010,0020 instead of 10, 20, etc).

    T12.CalcLineTotal 
    VATStmtLine2.SETFILTER("Row No.",VATStmtLine2."Row Totaling");

    This is quite a big headache, since on upgrading we have to identify all critical code and change some data.

    Any comments on that? I'm interested how this issue affected your upgrade projects. Thanks in advance.
  • Dduran
    Dduran Member Posts: 22
    Hi,

    the code field has a property that could help you. the property sqldatatype, if you change it to Integer, the filter works again.

    You can do it if your code field only has numeric values.

    Regards.
  • Phenno
    Phenno Member Posts: 630
    Dduran wrote:
    Hi,

    the code field has a property that could help you. the property sqldatatype, if you change it to Integer, the filter works again.

    You can do it if your code field only has numeric values.

    Regards.


    This helps in specific situations. Usually, ms sql sorting way is more useful.
    Standard problem with these sortings is on Chart of accounts where you have Total and Posting accounts. Totals are summed through flow fields (and flow filters). So, if you have a chart of accounts with accounts that have different "lenght (ex. 1310, 13101, 131001) than, you have a problem in Native db. You can't use Total fields with flowfields. Solution is to pad all the accounts to same lenght (ex. 131000, 131010, 131001).

    @ta5
    Yes, you are right as you can read in the first paragraph.

Categories