Blank parameters in AMDP procedure

[perage]

Hi,

I'm creating a customer search in an AMDP procedure and thus writing native SQL.

I have importing parameters like: kunnr, name1,street, pstlz, tel_number and so forth.

How can I write a query that supports these being blank?

E:g

SELECT kna1.kunnr, kna1.name1
WHERE kna1.kunnr = :iv_kunnr
AND   kna1.pstlz = :iv_pstlz

If I fill KUNNR but not PSTLZ I won't get any records if the customers postal code in DB is filled. I've also tried the code below after googling a bit, but it still doesn't work - to get a record, you need to populate both iv_kunnr and iv_pstlz

SELECT kna1.kunnr kna1.name1
WHERE ( :iv_kunnr IS NULL OR kna1.kunnr = :iv_kunnr )
AND   ( :iv_pstlz IS NULL OR kna1.pstlz = :iv_pstlz )

What I want is the native SQL equivalent of this:

DATA: lr_kunnr type range of kunnr,
      lr_pstlz type range of pstlz.

IF NOT iv_kunnr IS INITIAL.
  lr_kunnr = VALUE #( ( sign = 'I' option = 'EQ' low = iv_kunnr ) ).
ENDIF.

IF NOT iv_pstlz IS INITIAL.
  lr_pstlz = VALUE #( ( sign = 'I' option = 'EQ' low = iv_pstlz ) ).
ENDIF.

SELECT kunnr, name1 
 FROM kna1
 WHERE kunnr IN lr_kunnr
 AND   pstlz IN lr_pstlz.

Can it be done?

[DoanManhQuynh]

Did you try:

SELECT kna1.kunnr, kna1.name1 WHERE kna1.kunnr =:iv_kunnr OR kna1.pstlz =:iv_pstlz

And may be you can turn those parameter to table type and:

SELECT kna1.kunnr, kna1.name1 WHERE kna1.kunnr IN ( SELECT kunnr FROM :it_kunnr ) AND kna1.pstlz IN ( SELECT pstlz FROM :it_pstlz)

Or you can follow below blog to handle range selection:

https://blogs.sap.com/2015/03/30/handling-of-select-options-parameters-within-amdp/

[matt]

The range selection was what immediately came to mind when I read the question.

[DoanManhQuynh]

I think he want to avoid range since he only need to send a value, maybe OR could do the trick 🙂

[matt]

An Abap Managed Databse Procedure Procedure?

[perage]

Thanks for the reply.

OR won't work as it's important to limit the search by each parameter added. Select from it_kunnr was a good suggestion, but it doesn't work when it_kunnr is empty.

The solution however was this: (It seems that the parameters are never NULL, but when empty = '' works.

SELECT kna1.kunnr kna1.name1
WHERE ( :iv_kunnr = '' OR kna1.kunnr = :iv_kunnr )
AND   ( :iv_pstlz = '' OR kna1.pstlz = :iv_pstlz )