SQL SELECT CAST(myString AS INT) in x++

Finance and Operations/AX Developer Forum
1

Hi,

i have the following Table

myString

10

100

20

30

40

i need to retrieve the highest (integer!) value and increment by 10. It took me a while to figure out that it is sorted (10,100… and not 10,20,…,100) that’s why the following code breaks:

SELECT maxof(myString as int) FROM myTable;

this code will retrieve 40 and not 100.

Question: what can I do to get the 100? In sql you could use order by cast (myString AS int) but i do not get this to work in AX

Thx!!

2

I worked around the problem by converting the values into integer and writing it in a temporary table… would be great to know a better way eventually.

3

Hi,

you can’t. So either you change the field to integer or do a workaround like you already did. I’ve seen a workaround to a similar situation where the value of the string (‘10’) is replicated in a LineNum(10,000…) field and the LineNum is then used in ordering the records :slight_smile:

4

Temporary table is the solutions

5

You can store the values into the Set collection class and use SetIterator end() method to move the cursor at end and retrieve the last value from it. Since set will sort the values automatically during insertion.

Hope it will help you.

6

Hi Ramya,

your response answers my question but I can not use it for my purpose. I need to apply the solution on a table and the set collection can only store one field with multiple values (as far as i know)

i need to:

  1. Sort by myString

  2. find the hightest number

  3. return myString2

myString mystring2

10 aaaaa

100 bbbbb

20 ccccc

30 dddd

40 sklfjsdklf

→ record with the highest numeric value is 100 (not 40). return value needs to be “sklfjsdklf”

7

Why is it that you can’t do this in SQL?

8

thx for your reply.

have a look at my initial post (posted on Tue, Oct 23 2012 23:19). A string and a value is sorted differently (i guess it depends on the sql coalition… but in my case i can not change that anyways).