topic Re: Multiple Dynamic condition in SAP Query in Application Development and Automation Discussions

Multiple Dynamic condition in SAP Query

Dhivya — Tue, 15 May 2012 09:38:42 GMT

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

Re: Multiple Dynamic condition in SAP Query

RaymondGiuseppi — Tue, 15 May 2012 09:55:16 GMT

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

Re: Multiple Dynamic condition in SAP Query

former_member186055 — Tue, 15 May 2012 10:13:53 GMT

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

Re: Multiple Dynamic condition in SAP Query

Dhivya — Tue, 15 May 2012 10:28:33 GMT

Hi Raymond,

By using range how we can use multiple where condition?

Thanks,

Dhivya

Re: Multiple Dynamic condition in SAP Query

former_member186055 — Tue, 15 May 2012 10:58:25 GMT

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

Re: Multiple Dynamic condition in SAP Query

Former Member — Tue, 15 May 2012 11:03:05 GMT

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.

Re: Multiple Dynamic condition in SAP Query

RaymondGiuseppi — Tue, 15 May 2012 11:03:20 GMT

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

Re: Multiple Dynamic condition in SAP Query

Former Member — Tue, 15 May 2012 11:42:49 GMT

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..

Re: Multiple Dynamic condition in SAP Query

Dhivya — Tue, 15 May 2012 12:21:30 GMT

Hi Surya,

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

Thanks.,

Dhivya.

Re: Multiple Dynamic condition in SAP Query

Dhivya — Tue, 15 May 2012 12:33:02 GMT

Hi Raj,

This query will throw an error..

Thanks,

Dhivya

Re: Multiple Dynamic condition in SAP Query

former_member186055 — Tue, 15 May 2012 12:42:00 GMT

Dhivya,

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

Regards,

Surya