Multiple Dynamic condition in SAP Query

[Dhivya]

Hi,

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.

IF number is NOT INITIAL .

  SELECT * INTO CORRESPONDING FIELDS OF TABLE it_mara FROM mara WHERE matnr = number and mtart = type .

ELSEIF type is not INITIAL .

........

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

Thanks in advance.,

Dhivya.

Moderator message - Basic question locked

Message was edited by: Rob Burbank

[RaymondGiuseppi]

You could

- Convert PARAMETERS into SELECT-OPTIONS then use a IN for each (you can restrict the select-options via FM SELECT_OPTIONS_RESTRICT)

- Build RANGES similar to SELECT-OPTIONS (REFRESH range. if param IS not initial. range-sign = 'I'. range-option = 'EQ'. range-low = param. APPEND range. ENDIF.)

would be less effort than 2^5 IF/ENDIF)

Regards,

Raymond

[Dhivya]

Hi Raymond,

  By using range how we can use multiple where condition?

Thanks,

Dhivya

[former_member186055]

Raymond,

TABLES:
  mara.
DATA:
  t_mara TYPE STANDARD TABLE
      OF mara
 INITIAL SIZE 0.

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.

SELECT * FROM mara
  INTO TABLE t_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.

Regards,

Surya...SuryaPraveen

[RaymondGiuseppi]

When a range is empty, the value is not actually tested.

[Dhivya]

Hi Surya,

  In thsi case, if any field is null then we cant get output.

Thanks.,

Dhivya.

[former_member186055]

Dhivya,

If field null i.e., if you do not pass the selection field, for that field it'll consider all values.

Regards,

Surya

[former_member186055]

Hi Dhivya,

Instead of parameters use select-options as below:

data:
  lv_carrid type spfli-carrid..
select-options:
  s_carrid for lv_carrid no-extension no intervals.

Regards,

Surya...SuryaPraveen

[Former Member]

Hi Dhivya,

I hope the select query will not pass the null values in selecting the entries. I tried some of my codes and verified that there is no selection of null values from the DB table.

However if you want the code to avoid the null values explicitly, here is the code.

SELECT * FROM table INTO TABLE it_table
                      WHERE ( matnr = number AND not matnr is null )
                      AND   ( mtart = type AND not mtart is null )

                      AND    ( field = sector AND not field is null ).

Using this will avoid the null entries in select query. Check it and let me know if you face any difficulties.

-Geerthi

P.S: Select-option also good thing, but I have not tried for your conditions yet.

[Former Member]

Hi dhivya, pls go thru the below code, i hope it will be usefull.

tables mara .

  select-options  : number for MARA-MATNR MATCHCODE OBJECT MAT1 no intervals no-extension,

                    type for MARA-MTART MATCHCODE OBJECT H_T134 no intervals no-extension,

                    sector for MARA-MBRSH MATCHCODE OBJECT H_T137 no intervals no-extension,

                    group for MARA-MATKL MATCHCODE OBJECT H_T023 no intervals no-extension,

                    unit for MARA-MEINS MATCHCODE OBJECT H_T006 no intervals no-extension.

data IT_MARA type table of MARA.

select * from MARA into table it_MARA where MATNR in number

                                       and mtart in type

                                       and mbrsh in sector

                                       and matkl in group

                                       and meins in unit.

Regards,

Raj..

[Dhivya]

Hi Raj,

  This query will throw an error..

Thanks,

Dhivya