Sort table field from type character

[Former Member]

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

ID

1

10

2

3

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

Any ideas?

regards

[Former Member]

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

ID

1

10

2

3

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

Any ideas?

regards

[Former Member]

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

[Former Member]

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.

[Former Member]

Hi,

thanks.

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

We cannot change the type of the table.

regards

[Former Member]

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

[Former Member]

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

Thanks.

[Former Member]

Hai,

Here matnr is a character data type.

data matnr type matnr.

select max( matnr ) from mara into matnr.

write: matnr.

Regards,

Venkat

[Former Member]

>

> 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.