how can I select data similar to SQL WHERE LIKE from a parameter value

Question

I have the following CDS:

@AbapCatalog.sqlViewName: 'ZAMPAYERINFO'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Read payer information'
define view zam_payer_info
with parameters p_payer: abap.char(10)
as select distinct from knvp 
   join kna1 on
   knvp.kunnr = kna1.kunnr
{
    key knvp.kunnr as Payer,
    kna1.name1 as Name
} where knvp.kunnr like $parameters.p_payer

that the compiler complains:

Comparison value of LIKE condition must be a character-type literalZAM_PAYER_INFO (Data Definition)
As you can see on the CDS code, I am using character-type.

I understand, what LIKE statement do, for example:

@AbapCatalog.sqlViewName: 'ZAMPAYERINFO'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Read payer information'
define view zam_payer_info
  with parameters
    p_payer : abap.char(10)
  as select distinct from knvp
    join                  kna1 on knvp.kunnr = kna1.kunnr
{
  key knvp.kunnr as Payer,
      kna1.name1 as Name
}
where
  knvp.kunnr like 'a%'

Search all kunnr that starts with a. But my problem is, how to combine it with input parameter p_payer, at the end it should be LIKE %p_payer, where p_payer will be replace through the value that I passed.

What can be wrong?

In documentation, it says you just cannot use them together. https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abencds_cond_expr_like.htm — Oguz, Jan 15 '18 at 11:26
It says, it needs string literal so I can write only with quotation marks, right? — softshipper, Jan 15 '18 at 12:39
That is correct. However, you can try Eralper's solution, if you have HANA. — Oguz, Jan 15 '18 at 12:51
Possible duplicate of [Get all data that match to the start letter](https://stackoverflow.com/questions/48266222/get-all-data-that-match-to-the-start-letter) — Haojie, Jan 16 '18 at 01:41

score 0 · Answer 1 · answered Jan 15 '18 at 10:40

You can combine the input parameter and the '%' wildcard as follows in SQLScript for HANA databases

declare p varchar(10);
P = 'D';
select * from "A00077387"."ORGANIZATION" where unit like (:p || '%');

You see, you can use "||" to concatenate '%' to your parameter

You will need to modify your WHERE clause as follows

knvp.kunnr like ($parameters.p_payer || '%')

I hope it helps,

score 0 · Answer 2 · answered Jan 18 '18 at 10:29

You may push back the like statement the ABAP.

@AbapCatalog.sqlViewName: 'ZAMPAYERINFO'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Read payer information'
define view zam_payer_info
  with parameters
    p_payer : abap.char(10)   // you can remove this also.
  as select distinct from knvp
    join                  kna1 on knvp.kunnr = kna1.kunnr
{
  key knvp.kunnr as Payer,
      kna1.name1 as Name
}
//where
 //knvp.kunnr like 'a%'  remove the kunnr where statement here.

When you selecting from this cds view:

data lv_parameter type kunnr.
lv_parameter = 'A%'.
select * from ZAMPAYERINFO into table @data(lt_payer_info) 
   where name like lv_parameter.

It may seem a little bit lame, I think. It is a solution, though :)

how can I select data similar to SQL WHERE LIKE from a parameter value

2 Answers2