How to include multiple conditions in a SELECT?

Question

I am using 5 Parameters to fetch the details from DB table (mara, makt, marc, mard).

 PARAMETERS :number TYPE matnr MATCHCODE OBJECT MAT1 ,
             type TYPE MTART MATCHCODE OBJECT H_T134 ,
             sector TYPE MBRSH MATCHCODE OBJECT H_T137 ,
             group TYPE MATKL MATCHCODE OBJECT H_T023 ,
             unit TYPE MEINS MATCHCODE OBJECT H_T006 .

First I tried to fetch data from MARA table using the select query. In that to retrieve the particulare record, I have to use the WHERE condition. But I get confused in the condition part. We can check which parameter has value by using INITIAL condition.

But there is a chanced for 2/3/4/5 parameters to have values. For each case we have to write select query (if so it will lead to performance issue) or is there any way for using dynamic condition part in select query?

Solution 1

You can use SELECT-OPTIONS:

TABLES MARA.  

SELECT-OPTIONS:
  s_matnr FOR mara-matnr MATCHCODE OBJECT MAT1 ,
  s_mtart FOR mara-MTART MATCHCODE OBJECT H_T134 ,
  s_mbrsh FOR mara-MBRSH MATCHCODE OBJECT H_T137 ,
  s_matkl FOR mara-MATKL MATCHCODE OBJECT H_T023 ,
  s_meins FOR mara-MEINS MATCHCODE OBJECT H_T006 .

* [...]  

SELECT * FROM MARA where 
  matnr in s_matnr and
  mtart in s_mtart and
  mbrsh in s_mbrsh and
  matkl in s_matkl and
  meins in s_meins.

When you do so, you selection screen will allow multiple values and ranges for the data.

If you need single values like the parameter-command, you must set aditional options for the SELECT-OPTION:

• NO INTERVALS to allow only single values
• NO-EXTENSION to allow only one value.
• OBLIGATORY if an empty value is not allowed (As far I understand your question, you have the opposite situation, so you don't need it).

So your selection is:

SELECT-OPTIONS:
  s_matnr FOR mara-matnr NO-EXTENSION NO INTERVALS,
  s_mtart FOR mara-MTART NO-EXTENSION NO INTERVALS,
  s_mbrsh FOR mara-MBRSH NO-EXTENSION NO INTERVALS,
  s_matkl FOR mara-MATKL NO-EXTENSION NO INTERVALS,
  s_meins FOR mara-MEINS NO-EXTENSION NO INTERVALS.

Remark:

• MARA must be defined as a TABLE if you use SELECT-OPTIONS
• Do you really need the MATCHCODE OBJECT? Normally the usage of the FOR already defines the correct matchcode object (via data element/domain).

Disclaimer:

• I have no SAP-system available yet, so the code may contain syntax errors. - I will check it tomorrow.

Solution 2

I think the easiest way to do this is to use select-options instead. You can then use the select-option value with the in expression in your query.
That way, when the value is empty, it will automatically be ignored (which won't happen when you use an empty parameter in your query), so you won't have to create a separate WHERE expression for each possible combination. An example:

tables: mara.
select-options number for mara-matnr matchcode object mat1 no-extension no intervals.
select-options type for mara-mtart matchcode object h_t134 no-extension no intervals.
select-options sector for mara-mbrsh matchcode object h_t137 no-extension no intervals.
select-options group for mara-matkl matchcode object h_t023 no-extension no intervals.
select-options unit for mara-meins matchcode object h_t006 no-extension no intervals.

select distinct mara~matnr makt~maktx marc~werks mard~lgort into table ta_materials
  from mara
  inner join makt on makt~matnr = mara~matnr
  inner join marc on marc~matnr = mara~matnr
  inner join mard on mard~matnr = mara~matnr
  where makt~spras = sy-langu and
        mara~matnr in number and
        mara~mtart in type and
        mara~mbrsh in sector and
        mara~matkl in group and
        mara~meins in unit
  order by mara~matnr.

The no-extension and no intervals options will make the select-option behave like a parameter (more or less) on screen.

Comments

[René]

Looks like I had to type a bit faster ;)

[knut]

@René I wrote too much stuff - should only write Use select-options and complete the answer afterwards ;)