topic Re: Sort table field from type character in Application Development and Automation Discussions

Sort table field from type character

Former Member — Tue, 06 Oct 2009 11:22:39 GMT

Hi all,

I have a database table where a specific field (e. g. ID) is specified as character.

This field contains a number.

Now I want to get the highest ID which is available in the database table.

This statement does not work, because the max value is only determined by the first character.

SELECT single * FROM <table>

INTO <workarea>

WHERE id = (

SELECT MAX( id ) FROM <table>

Example: Entries in database table

The max value is here then 3 and not 10. -> only the first character will be checked

Any ideas?

regards

Re: Sort table field from type character

Former Member — Tue, 06 Oct 2009 11:40:04 GMT

Hi..

just try this code once and let me know.

select single *

from table

into wa_area

where <condition>

ORDER BY MAX DESCENDING ID.

All the best..

UR's

GSANA

Re: Sort table field from type character

Former Member — Tue, 06 Oct 2009 11:41:10 GMT

If it's a custom table, consider changing the field into a numeric field. Or... read the contents into an internal table, move the field into a numeric field and look for the max value.

Re: Sort table field from type character

Former Member — Tue, 06 Oct 2009 11:48:34 GMT

Hi,

thanks.

The coding does not work. Only the first character will be considered.

We cannot change the type of the table.

regards

Re: Sort table field from type character

Former Member — Tue, 06 Oct 2009 11:51:29 GMT

Or... read the contents into an internal table, move the field into a numeric field and look for the max value.

Re: Sort table field from type character

Former Member — Tue, 06 Oct 2009 11:52:46 GMT

This is the way I have realized it. But I thought there would be a better one!

Thanks.

Re: Sort table field from type character

Former Member — Tue, 06 Oct 2009 11:59:13 GMT

Hai,

Here matnr is a character data type.

data matnr type matnr.

select max( matnr ) from mara into matnr.

write: matnr.

Regards,

Venkat

Re: Sort table field from type character

Former Member — Tue, 06 Oct 2009 12:02:06 GMT


> Hai,
> 
> Here matnr is a character data type.
> 
> data matnr type matnr.
> 
> select max( matnr ) from mara into matnr.
> 
> write: matnr.
> 
> 
> 
> Regards,
> Venkat

Never forget: if a materialno. consist of digits only, the database value will have leading zeroes. And then it will work. In this case there are no leading zeroes, so character 3 is bigger than 10.

If there were leading zeroes, then 10 would be bigger then 03.