08-26-2009 12:28 PM
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
08-26-2009 12:38 PM
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
08-26-2009 12:38 PM
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
08-26-2009 12:38 PM
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.
08-26-2009 12:39 PM
Hi,
Use AND in WHERE condition. The select query will fetch records even if the optional fields are blank.
Regards,
Vik
08-26-2009 12:42 PM
Hi Sravanthi,
try this way.
Thanks
Venkat.O
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.
08-26-2009 12:46 PM
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.
08-26-2009 12:51 PM
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
08-26-2009 1:00 PM
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
08-26-2009 2:04 PM
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.