Writing smart SELECT queries

[Former Member]

I have had this problem for all my years as an ABAP programmer and am hoping someone here can help me with this.

Lets say the selection screen has 5 parameters. What is the best way to write queries to select data

Currently I would do something like this, but I dont think its the best way to code it.

parameters: p1, p2, p3, p4, p5. 

if p1 ne space and p2 ne space and p3 ne space and p4 ne space and p5 ne space. 
Select Data 

if p1 ne space and p2 ne space and p3 ne space and p4 ne space and p5 eq space. 
Select Data 

if p1 ne space and p2 ne space and p3 ne space and p4 eq space and p5 eq space. 
Select Data

And so on with all the other permutations. Isn't there a better way of handling this?

[Former Member]

I have had this problem for all my years as an ABAP programmer and am hoping someone here can help me with this.

Lets say the selection screen has 5 parameters. What is the best way to write queries to select data

Currently I would do something like this, but I dont think its the best way to code it.

parameters: p1, p2, p3, p4, p5. 

if p1 ne space and p2 ne space and p3 ne space and p4 ne space and p5 ne space. 
Select Data 

if p1 ne space and p2 ne space and p3 ne space and p4 ne space and p5 eq space. 
Select Data 

if p1 ne space and p2 ne space and p3 ne space and p4 eq space and p5 eq space. 
Select Data

And so on with all the other permutations. Isn't there a better way of handling this?

[Former Member]

Hello Megan,

Below code snippet can help you out:-

SELECT-OPTIONS  : s_matnr FOR iseg-matnr,
                    s_werks FOR ikpf-werks,
                    s_lgort FOR ikpf-lgort,
                    s_iblnr FOR ikpf-iblnr,
                    s_gjahr FOR ikpf-gjahr,
                    s_zldat FOR ikpf-zldat,
                    s_abcin FOR iseg-abcin.

 IF s_matnr IS INITIAL AND
     s_werks IS INITIAL AND
     s_lgort IS INITIAL AND
     s_iblnr IS INITIAL AND
     s_gjahr IS INITIAL AND
     s_zldat IS INITIAL AND
     s_abcin IS INITIAL.
    MESSAGE e000(zz) WITH text-e24.
  ENDIF.

SELECT werks lgort iblnr gjahr zldat sobkz bldat gidat budat
  FROM ikpf
  INTO CORRESPONDING FIELDS OF TABLE it_ikpf
  WHERE werks IN s_werks AND
        lgort IN s_lgort AND
        iblnr IN s_iblnr AND
        gjahr IN s_gjahr AND
        zldat IN s_zldat .

  DESCRIBE TABLE it_ikpf LINES res.
  IF res = 0.
    WRITE:/ 'No records matching the selection criteria'.
    EXIT.
  ENDIF.

  IF NOT it_ikpf[] IS INITIAL.
    SELECT iblnr zeili matnr abcin bstar buchm menge meins dmbtr
    waers mblnr mjahr wrtzl wrtbm
    FROM iseg
    INTO CORRESPONDING FIELDS OF TABLE it_iseg
    FOR ALL ENTRIES IN it_ikpf
    WHERE iblnr = it_ikpf-iblnr
    AND   matnr IN s_matnr
    AND   abcin IN s_abcin.
  ENDIF.

SORT it_iseg BY matnr ASCENDING.

  LOOP AT it_iseg INTO wa_iseg.
    MOVE-CORRESPONDING wa_iseg TO wa_output.

    READ TABLE it_ikpf INTO wa_ikpf
                       WITH KEY iblnr = wa_iseg-iblnr
                       BINARY SEARCH.
    IF sy-subrc = 0.
      MOVE:   wa_ikpf-werks  TO  wa_output-werks,
              wa_ikpf-lgort  TO  wa_output-lgort,
              wa_ikpf-iblnr  TO  wa_output-iblnr,
              wa_ikpf-gjahr  TO  wa_output-gjahr,
              wa_ikpf-zldat  TO  wa_output-zldat,
              wa_ikpf-sobkz  TO  wa_output-sobkz,
              wa_ikpf-bldat  TO  wa_output-bldat,
              wa_ikpf-gidat  TO  wa_output-gidat,
              wa_ikpf-budat  TO  wa_output-budat.
    ENDIF.
endloop.

.

You can proceed this way.

Manas M.

Edited by: Kumar Manas Mishra on Jan 20, 2010 4:31 PM

Edited by: Kumar Manas Mishra on Jan 20, 2010 4:37 PM

[Former Member]

Hi,

I usually work in the following way:

1. The obligatory fields, make them obligatory, that way you don't need to check if its blank.

2. Use select-options the most you can, that way its easier to code the select, even the user let the field in blank (meaning they will select everything for that field)

3. Finally if the field should be a single field (PARAMETER) then the only way is validating it, however with the first 2 steps you will only need to check these parameter fields.

For example:

IF p1 is initial.

SELECT * FROM BKPF INTO it_bkpf
WHERE belnr in so_belnr.

ELSE.

SELECT * FROM BKPF INTO it_bkpf
WHERE belnr in so_belnr
AND buzei = p1.

ENDIF.

Regards,

Gilberto Li

Edited by: Gilberto Li on Jan 20, 2010 4:34 PM

[kesavadas_thekkillath]

select-options:so_1 " add no intervals no-extension.

select-options:so_2 " add no intervals no-extension.

now in select query just pass the select option using 'IN'.

[Former Member]

Lesson learned. ... Use PARAMETERS as little as possible.

select-options:so_1 " add no intervals no-extension.

select-options:so_2 " add no intervals no-extension.

This should do the trick. Thank you all.

[Former Member]

I'm not sure I understand your question, but if you have multiple parameters, you should try to convert them to SELECT-OPTIONS, but you will also have to be careful to make sure the new logic works as intended.

Rob

[christine_evans]

You could set up a dynamic WHERE clause. But that would still involve using multiple IF statements.