Developers are sometimes faced with the need to sort character fields that contain variable length numeric data. For example, in this sample application, the database field "pieceserial" contains numeric product identifiers that range from 1000-999999. Simply doing an ascending sort of pieceserial will result in something like the following, which is not the desired end result:
1000
10000
1300
13456
1500
Worling with dbf files, the correct sort can be achieved simply by sorting on the value of pieceserial [i.e., val(pieceserial)]. However, to achieve the correct result in SQL requires that pieceserial be sorted as a numeric field because the portable SQL used by Alpha5 does not allow the use of a value function in select queries used to populate grids. But, suppose for various reasons you want to keep pieceserial as a character field [e.g., you don't want the dbms to replace null with zero]. Working directly with MySQL as the backend, you can execute the following select query to achieve the proper sort:
SELECT * FROM piece ORDER BY CAST(pieceserial AS UNSIGNED INTEGER);
Unfortunately, Alpha5 will not correctly parse this query. As noted in the Alphapedia on the "SQL Builder" page, "SQL expressions used in web publishing should conform to ... Portable SQL standards when working with non-ADO databases."
As it turns out the solution is rather simple. First, define a view using the following query [Note that the table name is piece]:
SELECT pieceserial, ..[other fields]..., CAST(pieceserial AS UNSIGNED) AS sortfieldname FROM piece
The view will contain a calculated integer field named "sortfieldname" that can be correctly sorted. The view can be used as the source for a grid component and the initial sort order can be expressed by either "sortfieldname ASC" or "sortfieldname DESC".
To properly sort the data when clicking the pieceserial column heading, simply place "sortfieldname" in the "Sort Field" property listed under "Column Properties" of pieceserial.
This approach should work with any dbms, but that has not been verified. In wrestling with the Alpha5 documentation to come up with this solution, I felt like I was caught in a Star Wars movie "going where no man has gone before." Hopefully, this will solve similar problems for other Alpha5 users.
Terry
1000
10000
1300
13456
1500
Worling with dbf files, the correct sort can be achieved simply by sorting on the value of pieceserial [i.e., val(pieceserial)]. However, to achieve the correct result in SQL requires that pieceserial be sorted as a numeric field because the portable SQL used by Alpha5 does not allow the use of a value function in select queries used to populate grids. But, suppose for various reasons you want to keep pieceserial as a character field [e.g., you don't want the dbms to replace null with zero]. Working directly with MySQL as the backend, you can execute the following select query to achieve the proper sort:
SELECT * FROM piece ORDER BY CAST(pieceserial AS UNSIGNED INTEGER);
Unfortunately, Alpha5 will not correctly parse this query. As noted in the Alphapedia on the "SQL Builder" page, "SQL expressions used in web publishing should conform to ... Portable SQL standards when working with non-ADO databases."
As it turns out the solution is rather simple. First, define a view using the following query [Note that the table name is piece]:
SELECT pieceserial, ..[other fields]..., CAST(pieceserial AS UNSIGNED) AS sortfieldname FROM piece
The view will contain a calculated integer field named "sortfieldname" that can be correctly sorted. The view can be used as the source for a grid component and the initial sort order can be expressed by either "sortfieldname ASC" or "sortfieldname DESC".
To properly sort the data when clicking the pieceserial column heading, simply place "sortfieldname" in the "Sort Field" property listed under "Column Properties" of pieceserial.
This approach should work with any dbms, but that has not been verified. In wrestling with the Alpha5 documentation to come up with this solution, I felt like I was caught in a Star Wars movie "going where no man has gone before." Hopefully, this will solve similar problems for other Alpha5 users.
Terry
Comment