ABAP CDS - concat_with_space - concatenate with dynamic spaces

[schwaninger]

Hello!

I would like to concatenate three character fields in a CDS view. The fields have a length of 2, 10 and 4 chars so the total length of the resulting string should always be 16 with spaces in the middle (after the second field).

• Line 1: AB && 12345 && CDEF
• Line 2: AB && 12345678 && CDEF

should get concatenated like this:

AB12345     CDEF
AB12345678  CDEF

I tried it like this:

      concat( field1, concat_with_space( field2, field3, 10 - length( field2 )))
           as NewField,

But I get an error "Function CONCAT_WITH_SPACE: at position 3, only Literals, possibly positive integers allowed". Using cast( 10 - length( field2 ) as abap.int1 ) as third parameter doesn't change the message.

Any idea how I can get the desired result? We use ABAP 7.53.

[schwaninger]

Thanks for your suggestion, yes in ABAP it is easy to get the result. But I am looking for an solution in an ABAP CDS view (HANA DB View).

[basantmhr]

Hi Jürgen Schwaninger,

As per the documentation, CONCAT_WITH_SPACE does not take a logical_expression as a parameter, it takes numeric literal only.

[Gourab_Dey]

It seems this is not feasible to do in CDS.

• The concate_with_space expects constant value
• I tried with other string function RPAD inside concat. It seems system is removing consequent space. If you want to fill '-', you can do it with this combination (rpad function inside concate). But your requirements is to fill space(similar functionality of respected space in concatenation statement in ABAP), which seems not feasible.

Thanks,

Gourab

[schwaninger]

Thanks for your answers, yes meanwhile I think too this is not possible currently. As you said, rpad can fill up with any character but space, but even a replace will remove them again

  replace(concat( field1, concat( rpad(field2,10,'#'), field3)), '#', ' ')
           as NewField // no errors, but also no spaces

So I found a workaround for me now as this concatenated string is also stored in a standard table. So I created a helping view to join the missing field into my view. But if anyone finds a solution for this in future I would still be interested as I need this kind of concatenates quite often.

[Gourab_Dey]

Yes, I already tried this before answering your question. The buttom line is, there cannot be consecutive multiple spaces inside any field. Ultimately, it will be removed by the system.

Thanks,

Gourab

[Kjeld1]

I had the same problem with inforecord characteristics which I wanted to fetch with CDS views. The key field objek in tabel ausp was a concatenation between inforecord, material and vendor. I managed to find a workaround for the dynamic spaces:

Create a helper view on mara which determines material number length:

define view ZI_SLST_MALE
  as select from mara
{
  key matnr,
      length(matnr) as length
}

Created a second helper view which does the concatenation. FYI I also had 10 spaces in front of the key field.

define view ZI_SLST_INOB
  as select from    eina
    left outer join ZI_SLST_MALE as material on material.matnr = eina.matnr
{
// This CDS view is required since concat_with_space with dynamic spaces is impossible....
  key eina.infnr,
      case when material.length = 1
           then cast( concat_with_space('', concat_with_space(eina.matnr, eina.lifnr, 39), 10) as cuobn )
           when material.length = 2
           then cast( concat_with_space('', concat_with_space(eina.matnr, eina.lifnr, 38), 10) as cuobn )
           when material.length = 3
           then cast( concat_with_space('', concat_with_space(eina.matnr, eina.lifnr, 37), 10) as cuobn )
           when material.length = 4
           then cast( concat_with_space('', concat_with_space(eina.matnr, eina.lifnr, 36), 10) as cuobn )
           when material.length = 5
           then cast( concat_with_space('', concat_with_space(eina.matnr, eina.lifnr, 35), 10) as cuobn )

This is not an elegant solution, but at least it fixed the last open requirement for my project.. 🙂

[Former Member]

Hi, I had exact problem just yesterday with substrings in CDS I wanted to use the SUBSTRING(ar1, pos, len)

arg1 is the string

pos- start position

len- length and I realised SAP does not let you use LENGTH(agr1)/ LENGTH(arg1) - 1 anything like that and that is for all SAP ABAP CDS string manipulation functions, so you have to use literal values and it would be nice to have some dynamic functionality there rather than having arguments. And I even opened a question- so going to close that there is no better way to do it. But just imagine if it is a data type of length 120 and you have a case like mine in the code below. Are we going to write case when 120 times?

  key   root.db_key                  as db_keyy,
        root.id                      as id,
        @DefaultAggregation: #SUM
        count( distinct id )         as incident_count,
        'NS'                         as company_code,
        case length(ltrim(root.id, '0'))
        when 0 then 'R'
        when 1 then 'R'
        when 2 then concat('R', substring(ltrim(root.id, '0') , 2, 1))
        when 3 then concat('R', substring(ltrim(root.id, '0') , 2, 2))
        when 4 then concat('R', substring(ltrim(root.id, '0') , 2, 3))
        when 5 then concat('R', substring(ltrim(root.id, '0') , 2, 4))
        when 6 then concat('R', substring(ltrim(root.id, '0') , 2, 5))
        when 7 then concat('R', substring(ltrim(root.id, '0') , 2, 6))
        when 8 then concat('R', substring(ltrim(root.id, '0') , 2, 7))
        when 9 then concat('R', substring(ltrim(root.id, '0') , 2, 8))
        when 10 then concat('R', substring(ltrim(root.id, '0') , 2, 9))
        when 11 then concat('R', substring(ltrim(root.id, '0') , 2, 10))
        when 12 then concat('R', substring(ltrim(root.id, '0') , 2, 11))
        when 13 then concat('R', substring(ltrim(root.id, '0') , 2, 12))
        when 14 then concat('R', substring(ltrim(root.id, '0') , 2, 13))
        when 15 then concat('R', substring(ltrim(root.id, '0') , 2, 14))
        when 16 then concat('R', substring(ltrim(root.id, '0') , 2, 15))
        when 17 then concat('R', substring(ltrim(root.id, '0') , 2, 16))
        when 18 then concat('R', substring(ltrim(root.id, '0') , 2, 17))
        when 19 then concat('R', substring(ltrim(root.id, '0') , 2, 18))
        when 20 then concat('R', substring(ltrim(root.id, '0') , 2, 19))
        else ltrim(root.id, '0')
        end                          as SpecialcaptureCd,<br>

[WRoeckelein]

I came now up with

replace( replace( concat_with_space( rpad( ltrim(marc.matnr, '0'), 40 , '#' ), marc.werks, 1 ), '#', ' #' ), '#', '') 

for stxl.tdname match which seems to work.

Elegant is something different however....

https://influence.sap.com/sap/ino/#/idea/287841 feel free to vote!

[bharatbajaj]

Genius... this did the trick for me.

Although it's not elegent and easy to understand with human eyes, but certainly works quite well.

this is how I used it for my requirement:

replace( replace( concat( concat(concat( rpad( ltrim(afpo.matnr, '0'), 18 , '#' ), afpo.pwerk ), afpo.verid), '##########S' ), '#', ' #' ), '#', '') as SOURCE_ID,

the outcome, with exact spaces in th middle as required:

1000008-000001    P0010001          S

Thanks,

Bharat