I have a field in a table, which is a string field. This field can have also space/empty values

In many cases this field is filled with an integer (converted to a string). I need to find the max integer value in the table for this field

This is a wrong solution...
-----------------------------------------------
long temp

select <field>
from <table>
where <field> <> ""
order by <field> desc
selectdo
temp = lval(strip$§(<field>))
if temp then
break
endif
endselect
-----------------------------------------------
...because, for example string "100" is less than string "99".

How can i do this?

I use baanIV.

Thanks a lot.

Is this for a report? More details are needed. One way (http://www.baanboard.com/baanboard/showthread.php?t=7171&highlight=string+sort) is to use qss.sort - do some searching on "sorting" and strings. You should find plenty of solutions.

Hi,

If getting the maximum number is the only requirement you can write something like this since lval converts the string into an integer.

long temp
temp = 0

select <field>
from <table>
where <field> <> ""
selectdo
if temp < lval(strip$§(<field>)) then
temp = lval(strip$§(<field>))
endif
endselect

Thanks srikarthy, this can be a solution.

I was wondering if there was a way to get this value just with the sql statement, something like "order by lval(<field>) desc".

Mark, I think the qss.sort is totally not necessary in this case, and therefor a serious waste of response time... (the sorting process is not very healthy for performance..)

Srikarthy's suggestion works fine, and is the best option towards performance.

I was wondering if there was a way to get this value just with the sql statement, something like "order by lval(<field>) desc". In a normal query this is not possible

Regards,
Eli Nager

Mark, I think the qss.sort is totally not necessary in this case, and therefor a serious waste of response time... (the sorting process is not very healthy for performance..)

Srikarthy's suggestion works fine, and is the best option towards performance.

In a normal query this is not possible

Regards,
Eli Nager
At this point I agree - but at the time I posted there really was enough information to determine what was really required. I mean no tables were mention, etc. - so sometimes it is just a shot in the dark with responses. :)