topic Re: Where condition with optional fields in select statement in Application Development and Automation Discussions

Where condition with optional fields in select statement

Former Member — Wed, 05 Aug 2009 07:56:16 GMT

Hi,

In a function module I have to fetch data based on 7 fields out of which 6 are optional.

If we won't enter values for optional fields it will take default values as zeroes .

So this is not fetching data. But for some fields some values has zero values.

How to solve this condition?

Regards,

maha.

Re: Where condition with optional fields in select statement

venkat_o — Wed, 05 Aug 2009 08:04:12 GMT

Hi Mahalaxmi, <li>Define Ranges like below which is used as SELECT-OPTIONS.

RANGES : R_MATNR FOR MARD-MATNR.
 SELECT * FROM MARD INTO TABLE IT_MARD WHERE MATNR IN R_MATNR.

Thanks Venkat.

Re: Where condition with optional fields in select statement

Former Member — Wed, 05 Aug 2009 08:07:48 GMT

pass that value to ranges as follows

data: r_vbeln for vbak-vbeln.

r_vbeln-sign = 'I'.

r_vbeln-option = 'EQ'.

r_vbeln-low = p_vbeln.

append r_vbeln.

use this r_vbeln like select options in select query.

Thanks,

Ajay

Re: Where condition with optional fields in select statement

rainer_hbenthal — Wed, 05 Aug 2009 08:08:34 GMT

There where condition can be dynamic. Read the online help for that.

Re: Where condition with optional fields in select statement

Former Member — Wed, 05 Aug 2009 08:13:18 GMT

Hi,

Sorry for incomplete data. They are not select options. But they are importparameters in function module.

Regards,

Maha

Re: Where condition with optional fields in select statement

rainer_hbenthal — Wed, 05 Aug 2009 08:17:00 GMT

So check if they are supplied (if para1 is supplied.) and build your where condition dynamically.

Re: Where condition with optional fields in select statement

Former Member — Wed, 05 Aug 2009 08:18:10 GMT

Hi Mahalakshmi,

Define a range and pass the data in the imort/export parameter to this range.

Use this range in the select query where condition instead of directly using the import/export parameter.

This will act like a select option and even though 6 fields are optional , you need to write only one select statement with all the 7 fields in the where condition.And YES, it will fetch values even though some of the fields are initial.


EXAMPLE.
 RANGES : R_kunnr FOR kna1-kunnr.
 Now pass the import parameter p_kunnr to this range.
 Here p_kunnr is optional.
  r_kunnr-sign = 'I'.
  r_kunnr-option = 'EQ'.
  r_kunnr-low = p_kunnr.
  append r_kunnr.

  SELECT * 
    FROM vbak 
    INTO TABLE IT_vbak 
WHERE kunnr IN R_kunnr.

Regards,

Vimal

Re: Where condition with optional fields in select statement

Former Member — Wed, 05 Aug 2009 08:24:34 GMT

Hello,

Try these options. Examples below.

1. NOT NULL (in the select statment).

SELECT * FROM SCUSTOM INTO WA_SCUSTOM WHERE TELEPHONE IS NULL.

ENDSELECT.

2. NOT INITIAL (after the select statement). Fetch the records based on Key field and then process it based on optional fields.

SELECT SINGLE * FROM MAR INTO WA_SCUSTOM WHERE MATNR IN R_MATNR.

IF <OPTIONAL FIELD> IS NOT INITIAL.

ENDIF.

Cheers,

Balaji

Re: Where condition with optional fields in select statement

Former Member — Wed, 05 Aug 2009 08:39:20 GMT

Hi,

For those import parameters for which its mentioned as optional, use like condition in the where clause of select statement in the function module.

try like this,



concatenate pfield2 '%' into pfield2.
concatenate pfield3 '%' into pfield3.
concatenate pfield4 '%' into pfield4.
concatenate pfield5 '%' into pfield5.
concatenate pfield6 '%' into pfield6.
concatenate pfield7 '%' into pfield7.

select * 
from db table 
into itab
where field1 = pfield1 and 
           field2  like pfield2 and 
           field3  like pfield3 and 
          .
          .
          .
           field7 like pfield7.

Now even if the optional fields are blank, data will be selected

Regards,

Vik

Re: Where condition with optional fields in select statement

Former Member — Wed, 05 Aug 2009 12:37:54 GMT

Hello,

You can use IS_SUPPLIED before select query to check if optional parameters are supplied or not and depending on the output you can form the select query.

Thanks,

Augustin.

Re: Where condition with optional fields in select statement

rainer_hbenthal — Wed, 05 Aug 2009 12:41:51 GMT

Thats why i gave you the hint with


  IS SUPPLIED

some postings ago

Re: Where condition with optional fields in select statement

Former Member — Fri, 18 Sep 2009 06:08:17 GMT

Hi,

You can have select-options declared for the optional fields instead of parameters.

If you still want to make them look like parameters, you can declare them like this

DATA spfli_wa TYPE spfli.

SELECT-OPTIONS s_carrid FOR spfli_wa-carrid NO-EXTENSION NO INTERVALS.

your select should be some thing like:

select * from <table> into table <intenal table>

where matnr in s_matnr.....

Hope this solves your problem

Regards,

Pavan.