Where condition with optional fields in select statement

[Former Member]

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.

[venkat_o]

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.

[Former Member]

Hi,

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

Regards,

Maha

[rainer_hbenthal]

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

[Former Member]

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

[Former Member]

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

[rainer_hbenthal]

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

[Former Member]

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

[Former Member]

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

[rainer_hbenthal]

Thats why i gave you the hint with

  IS SUPPLIED

some postings ago

[Former Member]

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.

[Former Member]

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.