Retrieving Sorted Data from CHAR Field with Numeric Value (SAP Basis 7.50+, S/4)

[yashoratna]

Introduction:

Let me share a curious situation I recently ran into. Imagine you need to fetch the maximum value from a table field. Easy peasy, right? Just use ORDER BY in your SQL SELECT statement and you're golden. Well, not so fast. That works perfectly when the field is of type NUMC, Integer, or Float. But what happens if the field is of type CHAR, even though it's storing numerical data?

I tried using ORDER BY DESCENDING on such a CHAR field. It worked fine for single-digit numbers (0-9). But as soon as it encountered two-digit numbers like 10, the magic broke. Suddenly, 1 > 10 (in SQL’s eyes). Oof! Frustrating, isn’t it?

So, what now?

Idea 1: Fetch All the Data

We could just pull all the records into a temporary internal table, sort them locally, and pick the first one. Problem solved, right? Well, not really. Why? Because:

1. We are fetching a ton of data we don’t even need.
2. If the table’s transactional data is massive, you’re looking at a potential performance bottleneck. Nobody wants their program slowing to a crawl!

Idea 2: Design Change

Should we redesign the field type? Or even nudge SAP to change it for us? Sure, that’s an option. But do we have that kind of time and is it not going to cause in any other problem? Probably not.

CAST to the Rescue! Now, this is where things get exciting! If you’re using ABAP release 7.50+ or S/4HANA, SAP has handed us a secret weapon: the CAST syntax. It’s like a Swiss Army knife for your SELECT statements. Here’s the game plan:

1. First, we use CAST to convert the CHAR field into NUMC.

2. Then, like a magic trick with an encore, we chain another CAST to transform NUMC into Integer (or INT8).

Yes, you heard me right—chaining CAST statements is your golden ticket! It’s like building a performance-boosting machine one layer at a time. Let’s dive into the action.

For example, I needed to select the max value from Field TEXT3 of table VTTK (Shipment Header). This field is CHAR40. So I constructed the Select statement with Open SQL expressions and by use of CAST as per below.  Here lv_text3 is the maximum value stored in a numerical format.

             SELECT
             FROM vttk
             FIELDS CAST( CAST( text3  AS NUMC( 40 ) ) AS INT8 ) AS text3
             WHERE text3 NE @space
             ORDER BY text3 DESCENDING
             INTO @DATA(lv_text3) UP TO 1 ROWS.
             ENDSELECT.
*OR
             SELECT
             FROM vttk
             FIELDS MAX( CAST( CAST( text3  AS NUMC( 40 ) ) AS INT8 ) ) AS text3
             WHERE text3 NE @space
             INTO @DATA(lv_text3).

And just like that, we have turned a tricky problem into a smooth, high-performance solution. No unnecessary data fetching. No frustrating bottlenecks. Just elegant, efficient ABAP magic. 

This ends the functionality.

Appreciate you stopping by and reading the article! Your comments, suggestions, and shares are greatly appreciated.

[Sandra_Rossi]

Thanks for the tip.

Generally speaking, it works like that in "all" programming languages. It happens for any comparison like in SORT and IF, e.g. the text '2' is greater than '10' because it compares one character at a time ('2' is greater than '1' and the comparison stops). If numeric types are used for the comparisons, the number 2 is lower than 10 as expected.

Concerning the database table columns containing numbers, it would be better to define columns of type numeric instead of character, of course. In your case, VTTK is very old design (custom fields were provided by SAP to contain anything). Hopefully, it's been a while that custom fields are to be appended to the tables when needed, any type can be chosen.

Note that CAST from CHAR to NUMC is not sufficient alone (you need a second CAST to a numeric type e.g. INT8 as you proposed), because the characters are copied without any transformation, the spaces and other non-numeric characters are kept as-is, etc.: "There is no special handling for the special target type for numeric text, NUMC, and for the date/time types DATS and TIMS. It is advisable to only convert valid values" (ABAP doc > CAST > Source Types CHAR, SSTRING > Character-like target type (e.g. NUMC)).

As you say, the construct "CAST( CAST( char AS NUMC( 40 ) ) AS numerictype )" is magic, also because NUMC is known to support only the digits 0 to 9 but CAST keeps the negative sign and the decimal point! "The validity of the content of the source field [of type NUMC] is not checked, which means that there can be decimal places." (ABAP doc > CAST > Source Type NUMC > Numeric target type).

Limitation: INT8 supports up to 19 digits (values from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807).

FLTP can be used instead of INT8 but the CAST keeps only 16 representative digits, so the comparison may be inexact if the number has 17 or more digits (e.g. '123456789012345670' and '123456789012345671' are both rounded to the same number 1.2345678901234566E+17).

Cast from NUMC to DECFLOAT34 is available since 7.54. It's the only solution to compare exactly numbers up to 34 digits.

NB: for NUMC, CAST of CDS Views has exactly the same behavior as ABAP SQL: ABAP Keyword Documentation > CAST > Character-Like Source Type.

[yashoratna]

Thank you so much, Sandra_Rossi , for an absolutely brilliant explanation! Your detailed analysis and clear examples made the concept crystal clear, and I appreciate the effort you put into crafting such a comprehensive and helpful response.