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

where condition with optional fields in a select statement

Former Member — Wed, 26 Aug 2009 11:28:01 GMT

Hi,

I have 7 optional fields on the selection screen of a Report. While fetching the data from database table, how do we include those optional fields in WHERE condition of the select query?

If I use AND in WHERE condition of the select query: If I do not enter any values for the optional fields ,it will not fetch any data.

If I use OR : It will fetch wrong(unwanted) entries.

Please help me to sovle this.

Thanks in advance,

sravanthi

Re: where condition with optional fields in a select statement

former_member404244 — Wed, 26 Aug 2009 11:38:24 GMT

Hi,

Is the selection screen fields are SELECT-OPTIONS or PARAMTERS??

Always use select-options so that u can get all the records if nothing specified on the screen

You can also do like this as well

Select-options : s_matnr for mara-matnr no extensions no-intervals. This will look like as paramter only..

Now u can use this in select query

select * from mara into table itab

where matnr in s_matnr

and mtart in s_mtart.

Regards,

Nagaraj

Re: where condition with optional fields in a select statement

Former Member — Wed, 26 Aug 2009 11:38:53 GMT

Hi ,

Please use select options names based on your requirement in select statement.

Use AND operation in select statement as select option provides option to exclude or include values.

Also, you can consult functional for this.

Hope it helps you.

Re: where condition with optional fields in a select statement

Former Member — Wed, 26 Aug 2009 11:39:41 GMT

Hi,

Use AND in WHERE condition. The select query will fetch records even if the optional fields are blank.

Regards,

Vik

Re: where condition with optional fields in a select statement

venkat_o — Wed, 26 Aug 2009 11:42:13 GMT

Hi Sravanthi, try this way.


REPORT ztest.

DATA wa_spfli TYPE spfli.

SELECT-OPTIONS s_airline FOR wa_spfli-carrid NO INTERVALS
                                           NO-EXTENSION. "It looks like parameter. It serves your purpose.
START-OF-SELECTION.
SELECT * FROM SPFLI INTO TABLE IT_SPFLI WHERE AIRLINE IN s_airline.

Thanks Venkat.O

Re: where condition with optional fields in a select statement

Former Member — Wed, 26 Aug 2009 11:46:36 GMT

pls see the below sample query.

definitely it will work.

SELECT vbeln "Quotation no

erdat " Creation Date

ernam "Creator

angdt "Valid From

bnddt "Valid To

knumv

auart "Document type

audat "Document Date

vtweg "Distribution channel

vkorg "Sales Organization

kunnr "Customer no

FROM vbak INTO TABLE gt_vbak

WHERE vbeln IN so_vbeln AND

erdat IN so_date AND

auart EQ text-t20 AND

vtweg EQ pa_vtweg AND

vkorg IN so_vkorg.

Re: where condition with optional fields in a select statement

Former Member — Wed, 26 Aug 2009 11:51:49 GMT

Hi,

You are saying optional fields are in selection-screen, To fetch the data from select query you have to give all necessary selection screen fields in where condition either it is a optional or mandatory in selection screen. If it is the optional selection screen field but if it is necessary to use in where condition you have to use, if it is optional selection screen field does not give any values to that field, no problem it will pulls all records which are matched.

Regards,

Ganesh

Re: where condition with optional fields in a select statement

Former Member — Wed, 26 Aug 2009 12:00:19 GMT

Hi,

You can use the following code

TYPES: BEGIN OF lty_vbeln,

sign(1) TYPE c,

option(2) TYPE c,

low TYPE vbak-vbeln,

high TYPE vbak-vbeln,

END OF lty_vbeln,

BEGIN OF lty_erdat,

sign(1) TYPE c,

option(2) TYPE c,

low TYPE vbak-erdat,

high TYPE vbak-erdat,

END OF lty_erdat.

DATA: lt_vbeln TYPE STANDARD TABLE OF lty_vbeln,

lt_erdat TYPE STANDARD TABLE OF lty_erdat.

DATA: lw_vbeln TYPE lty_vbeln,

lw_erdat TYPE lty_erdat.

IF NOT p_vbeln IS INITIAL.

CLEAR lw_vbeln.

lw_vbeln-sign = 'I'.

lw_vbeln-option = 'EQ'.

lw_vbeln-low = p_vbeln.

APPEND lw_vbeln TO lt_vbeln.

CLEAR lw_vbeln.

ENDIF.

IF NOT p_erdat IS INITIAL.

CLEAR lw_erdat.

lw_erdat-sign = 'I'.

lw_erdat-option = 'EQ'.

lw_erdat-low = p_erdat.

APPEND lw_erdat TO lt_erdat.

CLEAR lw_erdat.

ENDIF.

SELECT vbeln erdat ....

FROM vbek

INTO TABLE lt_vbak

WHERE vbeln IN lt_vbeln

AND erdat IN lt_erdat.

This always help when you have optional field on screen.

Let me know if you need more info.

Regards

Krunal Raichura

Re: where condition with optional fields in a select statement

Former Member — Wed, 26 Aug 2009 13:04:50 GMT

Use selecti option then you can fetch the data even if you dont enter any value in that field.

Example

select-options: s_matnr for mara-matnr,

s_spart for mara-spart.

Select matnr form mara into table itab

where matnr in s_matnr and

spart in s_spart.

Now if you are using parameter then you need to append the value of parameter into a range table and then use this range table in your select query.

parameter: p_matnr like mara-matnr.

ranges: r_matnr for mara-matnr.

r_matnr-sign = 'I'.

r_matnr-option = 'EQ'.

r_matnr-low = p_matnr.

appned r_matnr.

Select matnr form mara into table itab

where matnr in r_matnr.

Hope thie will help you.