how to use wildcard with for all entries in sql

[former_member625844]

I have an itab gt_alv with a column product_id. I want to query from table PRPS in which column usr00 contain the product_id . So I wrote below query.

SELECT fkstl, posid  FROM prps FOR ALL ENTRIES IN @gt_alv WHERE usr00 LIKE concat( '%', @gt_alv-product_id, '%' ) INTO TABLE @DATA(gt_aufk).

But it has error "LIKE xxxx can only be specified in a type c field". So is wildcard allowed here? If not, I will query all result from PRPS and loop through it match the usr00, is there a better way? Thx.

[Sandra_Rossi]

See the ABAP documentation. Only =, >, ..., EQ, GT, ... are supported. Others are not (like LIKE, BETWEEN...)

Your query seems very "dangerous" from performance perspective, as it requires a full loading of PRPS table. Maybe in-memory database will better support if USR00 is stored in memory, but your query might be very very slow. Maybe you'd better have a separate table or an "index table" constructed each time you update PRPS-USR00, or at regular intervals...

The question to clarify first, where does the value gt_alv-product_id come from, why do you need LIKE %...% ?

[former_member625844]

@sandra.rossi IThx for comment .The data in gt_alv are fetched from an oracle db through dbco. I need add $% because the product_id may be "0022A" and in prps I want to fetch all those contain 0022A such as "0021A/0022A".

[Sandra_Rossi]

Sorry, I didn't mean what is "LIKE" for, but I meant why the solution isn't designed to avoid a full scan search.

If it's something in place for a long time, then you'd better do a PRPS full scan, and then filter in ABAP. Of course, it will be slow, but not worse than what you're trying to do.

" BEWARE !!! Full scan -> very low performance

SELECT kfstl, posid, usr00 FROM prps PACKAGE SIZE 1000 INTO TABLE @DATA(package_prps).
  LOOP AT package_prps ASSIGNING FIELD-SYMBOL(<prps>).
    SPLIT <prps>-usr00 AT '/' INTO TABLE @DATA(usr00_parts).
    DATA(found_in_alv) = abap_false.
    LOOP AT usr00_parts ASSIGNING FIELD-SYMBOL(<usr00_part>).
      IF line_exists( gt_alv[ USING by_product_id COMPONENTS product_id = <usr00_part> ] ).
        found_in_alv = abap_true.
        EXIT.
      ENDIF.
    ENDLOOP.
    IF found_in_alv = abap_true.
      APPEND <prps> TO table_prps.
    ENDIF.
  ENDLOOP.
ENDSELECT.

If you prefer a better-performing solution, you'd better have a separate table or an "index table" constructed each time you update PRPS-USR00, or at regular intervals...

Of course, I don't know the logic you have implemented to fill PRPS-USR00 and your Oracle table, so only you can find the best solution.