X++ query to SELECT MAX value of String type field

Question

I have a field in Dynamics AX 2012 table populated with serial number of PRE1 00162 format and I need to return next serial number, which in this case would be PRE1 00163.

On a legacy system it is achieved by running

SELECT MAX(RIGHT(SerialNumber,5))+1 FROM Table_Serials 
WHERE SerialNumber LIKE 'PRE1%' 

against the table on SQL server.

How can I achieve same result in X++? My guess so far is

select maxof(right(SerialNumber,5))+1
    from tableSerials
    where tableSerials.SerialNumber 
like tableSerials;

but it shows syntax error starting right after maxof(right( part.

Thank you!

Answer 1

One of my wise colleagues have also suggested this method, which also works:

static void CompNextSerial(Args _args)
{
    str value;
    int num;
    str 10 prefix;
    SerialsTable serialsTable;

    prefix = "PRE1 *";

    select firstOnly serialsTable
        order by SerialNumber desc
        where serialsTable.SerialNumber like prefix;

    value = subStr (serialsTable.SerialNumber, strLen(serialsTable.SerialNumber) - 4 , 5);
    num = str2int(value) + 1;
    info (strFmt('%1', num));
}