01-12-2007 10:07 PM
Hi All,
I am hitting 4 tables for selecting some data using inner join. There are three user
input fields which if filled, has to take part in data selection. These three input fields are independent of each other.
so while writing select query, how can i take care of all the possiblilities(none of them filled -- every one of them filled ) in the
most efficient way..?
Thanks
-pranati
01-12-2007 10:23 PM
Hi,
IN the select-options you can add NO-EXTENSION NO INTERVALS..Which can be used a parameter..And you have to use IN in the select statement to filter the data..
Check this example
TABLES: marc.
<b>SELECT-OPTIONS: so_matnr FOR marc-matnr NO-EXTENSION NO INTERVALS.
SELECT-OPTIONS: so_werks FOR marc-werks NO-EXTENSION NO INTERVALS.</b>
DATA: t_marc TYPE STANDARD TABLE OF marc.
START-OF-SELECTION.
Get the first 100 rows.
SELECT mandt matnr werks UP TO 100 ROWS
INTO TABLE t_marc
FROM marc
<b> WHERE matnr IN so_matnr
AND werks IN so_werks</b>.
IF sy-subrc = 0.
WRITE: / 'RECORD COUNT - ', sy-dbcnt.
ELSE.
WRITE: / 'NO RECORD FOUND'.
ENDIF.
Thanks,
Naren
01-12-2007 10:11 PM
I guess you want to use SELECT-OPTIONS rather than PARAMETERS.
You do have to be a bit careful with SELECT-OPTIONS though. If the SELECT-OPTION is a key field and is left blank by the user, then the SELECT may not be able to make efficient use of an index.
Rob
Message was edited by:
Rob Burbank
01-12-2007 10:12 PM
make sure that you give the user input through a select-option only and not a parameter. If you have select options then the select statement will take care of all the possibilities you have mentioned.
- Guru
Reward points for helpful answers
01-12-2007 10:23 PM
Hi,
IN the select-options you can add NO-EXTENSION NO INTERVALS..Which can be used a parameter..And you have to use IN in the select statement to filter the data..
Check this example
TABLES: marc.
<b>SELECT-OPTIONS: so_matnr FOR marc-matnr NO-EXTENSION NO INTERVALS.
SELECT-OPTIONS: so_werks FOR marc-werks NO-EXTENSION NO INTERVALS.</b>
DATA: t_marc TYPE STANDARD TABLE OF marc.
START-OF-SELECTION.
Get the first 100 rows.
SELECT mandt matnr werks UP TO 100 ROWS
INTO TABLE t_marc
FROM marc
<b> WHERE matnr IN so_matnr
AND werks IN so_werks</b>.
IF sy-subrc = 0.
WRITE: / 'RECORD COUNT - ', sy-dbcnt.
ELSE.
WRITE: / 'NO RECORD FOUND'.
ENDIF.
Thanks,
Naren
01-12-2007 11:33 PM
Hi Naren,
Thank You .. That has solved my problem.. !
Thanks
-Pranati.