Native to SQL ... now records not FILTERing in proper order!

ecarmody

Hello,

We've always been using a CODE (10) type field on a table that holds basically what is a order number. We have two types of orders where one type is in range 1,000,000 to 9,999,999 and the other type is ranged 99,999,999 and greater.

No comma's ... I'm just showing them for clarity of the numeric value.

Problem. Since converting to SQL, they do not sort like a numeric sequence anymore. I see the table in the SQL has defined the field a VARCHAR, so now they sort left to right alphanumerically. So, 4,000,000 sorts AFTER 33,000,000 !!!

Can I achieve a sort using the SETFILTER or SETRANGE so I get the orders greater than 9,999,999 ?

I haven't figured out a way to use a function in the SETFILTER or SETRANGE command, such as filtering where STRLEN(no) > 7; or perhaps padding with leading zeros, where 04000000 would be less than 33000000.

Any help would be very apprecated.

Cheers,
Eric

Albertvh

Hi Eric,

Look at this Sorting Issues after conversion to SQL... version 4.0

Hope the link works if not look in the SQL General forum.

Albert

todro

ecarmody wrote:

Hello,

We've always been using a CODE (10) type field on a table that holds basically what is a order number. We have two types of orders where one type is in range 1,000,000 to 9,999,999 and the other type is ranged 99,999,999 and greater.

No comma's ... I'm just showing them for clarity of the numeric value.

Problem. Since converting to SQL, they do not sort like a numeric sequence anymore. I see the table in the SQL has defined the field a VARCHAR, so now they sort left to right alphanumerically. So, 4,000,000 sorts AFTER 33,000,000 !!!

Can I achieve a sort using the SETFILTER or SETRANGE so I get the orders greater than 9,999,999 ?

I haven't figured out a way to use a function in the SETFILTER or SETRANGE command, such as filtering where STRLEN(no) > 7; or perhaps padding with leading zeros, where 04000000 would be less than 33000000.

Any help would be very apprecated.

Cheers,
Eric

You can set the SQL data type property of the field to INTEGER if you are sure you are only using integers. As a restriction, you can not use the alternative searchfield to search for a alphanumeric alternative in this case.

To get around this, I 'd suggest to change the field property SQL Data Type to VARIANT, this will cover both.