11/25/2024 5:22:43 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
String and integer ordering for custom auto_increment
Posted: 27 Jun 08 9:00 AM
|
Hi guys,
I have got an issue with finding the maximum value in a column. The column is filled with id#'s for customers, only in numerical form, that increments by one each time. However, this is not an auto-increment field as it is a size 32 string column. Thus, when sorting receiving the results from my query, 865 is larger than 1006. I have tried using CAST( IDNUM as integer ) in the query as well. Any ideas on how, in the architect, I can sort the strings by their numeric value not alphabetic by SQL? I suppose I could return all of the records, write a custom sorting routine in VB (which I don't really know- I am a C/C++ man) but that is an inelegant solution as this will be going on the remote sales laptops and that seems like it would hinder performance.
I am open to trying to convert the field to an integer, however, I do not know how to do this and we do not have documentation for SLX 7.2 (which we use) only 6.2 - and even then we are missing some of the resources.
I check for null, empty, and when I got the results, i sometimes got an odd "unfiled" as the response, so I removed it to. I get the correct answer for all strings with the same number of digits.
Any help would be appreciated,
P
code:
Set objNum = objSLXDB.GetNewRecordSet
objNum.Open "Select CUSTOMERNUMBER From ACCOUNTEXTENSION Where CUSTOMERNUMBER IS NOT NULL AND CUSTOMERNUMBER <> '' AND CUSTOMERNUMBER <> 'unfiled' ORDER BY (CAST (CUSTOMERNUMBER as Integer)) DESC", objSLXDB.Connection
if NOT IsEmpty(objNum) Then
msgbox "query_ran"
msgbox objNum.Fields(0).Value
else
msgbox "Nothing returned"
end if
objNum.Close
|
|
|
|
Re: String and integer ordering for custom auto_increment
Posted: 27 Jun 08 9:08 AM
|
objNum.Open "Select CUSTOMERNUMBER From ACCOUNTEXTENSION Where CUSTOMERNUMBER IS NOT NULL AND CUSTOMERNUMBER <> '' AND CUSTOMERNUMBER <> 'unfiled' ORDER BY CUSTOMERNUMBER DESC", objSLXDB.Connection
This is the version that sorts correctly, the above query does not execute. |
|
|
|
Re: String and integer ordering for custom auto_increment
Posted: 01 Jul 08 2:10 PM
|
I have found an acceptable solution, for up to 99,999 entries (~95k new ones for the company).
Select beta.CUSTOMERNUMBER From ACCOUNTEXTENSION as beta
Where beta.CUSTOMERNUMBER IN
(Select alpha.CUSTOMERNUMBER From ACCOUNTEXTENSION as alpha
Where alpha.CUSTOMERNUMBER IS NOT NULL AND alpha.CUSTOMERNUMBER <> '' AND alpha.CUSTOMERNUMBER <> 'unfiled' AND
LEN(alpha.CUSTOMERNUMBER) = 4)
OR beta.CUSTOMERNUMBER IN
(Select gamma.CUSTOMERNUMBER From ACCOUNTEXTENSION as gamma
Where gamma.CUSTOMERNUMBER IS NOT NULL AND gamma.CUSTOMERNUMBER <> '' AND gamma.CUSTOMERNUMBER <> 'unfiled' AND
Len(gamma.CUSTOMERNUMBER) = 5) ORDER BY Len(beta.CUSTOMERNUMBER)
Desc, beta.CUSTOMERNUMBER Desc
I just grab the entry off the top.  |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|