Add leading zeros only to numeric field in CDS?

Question

In my AS ABAP 7.50 system, I have a table where the material length is 18 and I need to expose it via CDS as if the material length was 40 like in S/4. The material IDs in the system can be numeric (with leading zeros) or alphanumeric. The material field needs to be casted to MATNR40, and if the ID is numeric, the leading zeros need to be added up to the 40 characters.

First, I tried `lpad. But of course, it also adds the leading zeros to the alphanumeric values:

lpad( cast(matnr as matnr40), 40, '0' ) as material_long,

Then I added a case but I'm not able to make the condition work as I expect. As konstantin confirmed in the comments, it's not possible to use regex here as I attempted:

case when matnr like '%[^0-9.]%'
     then lpad( cast(matnr as matnr40), 40, '0' )
     else cast(matnr as matnr40)
end as material_long,

Is there a solution within the CDS itself to this problem?

Source table:

MATNR18	Description
000000000000000142	Numeric ID material
MATERIAL_2	Alphanumeric ID

Expected result:

MATNR40	Description
0000000000000000000000000000000000000142	Numeric ID material
MATERIAL_2	Alphanumeric ID

Is the data just being read (limit/offset/all) or is the column of interest also filtered or used in some more complex queries? Because if the data you're trying to convert are used in any even simple filters (like OData) these filters probably fail to "understand" your casts, case structures, regex and all. Your initially well-intentioned and concise CDS code might then become a huge, hard-to-debug performance bottle neck with lots of unforseeable bugs. You might be better off writing an (unittested) DPC_EXT method that does the conversion for you than trying to solve this in "plain" CDS/SQL. — koks der drache
BTW: The LIKE comparison does not understand regular expressions. It's more wildcards but nothing fancy like you're trying to do. See the official sap documentation regex vs. like. If you'd like to use the regex in some ABAP code you could use ^\d+$ to match only numerical values or NOT LIKE %_% in SQL/CDS if all non-numerical values contained an underscore. — koks der drache
@konstantin the problem is that some tables in the system already have the 40 characters length and I need to create joins and associations between tables using different field length. I'm not allowed to change the field length at this point so for now I just wanted to solve it in a CDS. Otherwise I will just do intermediate processing in ABAP and use FOR ALL ENTRIES... — RaTiO

astentx astentx · Accepted Answer · 2021-01-12T13:58:26

Due to the limited functionality in CDS syntax the only way I see is to nest 10 REPLACE functions to remove digits and compare the result with initial string. If it is initial, then you have only digits, so you can LPAD them with zeroes. If not - use the original value.

Here's my code:

@AbapCatalog.sqlViewName: 'Z_V_TEST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Test'
define view Z_TEST as select from /bi0/mmaterial {
    cast(
      case replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(material,
        '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '')
        when ''
        then lpad(material, 40, '0')
        else material
      end as abap.char(40)
    ) as MATERIAL_ALPHA,
    material
}

And the result is:

REPORT Z_TEST.

    select *
    from Z_V_TEST
    where material in ('LAMP', '000000000000454445')

      into table @data(lt_res)
    .
    cl_demo_output=>display( lt_res ).



MATERIAL_ALPHA                           | MATERIAL 
-----------------------------------------+-------------------
0000000000000000000000000000000000454445 | 000000000000454445 
LAMP                                     | LAMP

Add leading zeros only to numeric field in CDS?

1 Answers