topic Re: Parameters in Application Development and Automation Discussions

Parameters

Former Member — Wed, 19 Jul 2006 15:03:49 GMT

Hi All,

If a parameter is used in a select statement and if it is initial. how will it be treated.

I mean:

select * from keko into table xkeko

where matnr in s_matnr

and werks in s_werks

and kokrs eq p_kokrs

and klvar EQ p_klvar

and tvers EQ p_tvers

and kadky EQ p_kadky

and losgr EQ p_losgr

order by kalnr kadky descending.

If some of the parameters above are not filled with a value and are initial ; will the records being pulled be looked for a blank value in those parameters or used as a wildcard.

Thanks in advance..!

Re: Parameters

Former Member — Wed, 19 Jul 2006 15:05:51 GMT

Hello,

If the parameter is initial, the select querz will serach for the blank value in the table for that field.

So if u want to pick all the record when it is blank then built a range table and pass to the querz.

If useful reward.

Vasanth

Re: Parameters

RichHeilman — Wed, 19 Jul 2006 15:14:23 GMT

The select statement will only bring records which the values are blank. If you want it to bring all if the field is blank, like wild card, you should use SELECT-OPTIONS that look like parameters.




select-options: s_bukrs for t001-bukrs no intervals no-extension.

Select 
   .....
      where bukrs in s_bukrs.

Regards,

Rich Heilman

PS.

Put yourself on the SDN world map (http://sdn.idizaai.be/sdn_world/sdn_world.html) and earn 25 points.

Spread the wor(l)d!

Re: Parameters

Former Member — Wed, 19 Jul 2006 15:17:09 GMT

Hi,

if parameter is blank in the select

then

its initial value will be used for selection

if parameter is date ...00000000 will be used in select query

if Time then ......000000

if char1.........then space.

if int ...........0.

In case of select-option ....it will be drop form the select query.

in case of Like ....it will be dropped from the select query

Re: Parameters

Former Member — Wed, 19 Jul 2006 15:33:02 GMT

Hi,

<b>No DATA will be fetched when parameters is initial.</b>

Consider this code.


REPORT zztest_arun.

TABLES : mara.

DATA : it_mara_par TYPE STANDARD TABLE OF mara,
       it_mara_sel TYPE STANDARD TABLE OF mara,
       par_cnt TYPE i,
       sel_cnt TYPE i.

PARAMETERS     : p_matnr TYPE mara-matnr.
SELECT-OPTIONS : s_mtart FOR mara-mtart.


START-OF-SELECTION.
  SELECT * FROM mara INTO TABLE it_mara_par UP TO 200 ROWS WHERE matnr EQ p_matnr AND
                                                                 mtart IN s_mtart.

  DESCRIBE TABLE it_mara_par LINES par_cnt.

*Lets clear parameter and see the result
  CLEAR p_matnr.

  SELECT * FROM mara INTO TABLE it_mara_par UP TO 200 ROWS WHERE matnr EQ p_matnr AND
                                                                 mtart IN s_mtart.

  DESCRIBE TABLE it_mara_sel LINES sel_cnt.

  WRITE : / 'No of Records Fetched',
          / 'PARAMETERS WITH DATA    :' , par_cnt,
          / 'PARAMETERS WITH NO DATA :' , sel_cnt.

<b>The reason is that.</b>

1)Select-options with "NO INTERVALS NO-EXTENSION" and PARAMETERS are displayed as the same on the selection screen then why they are used??

When you dint give any value in selection screen for PARAMETERS, none of the records will be fetched when it is used in the WHERE clause of a SELECT statement. But when no input is given in SELECT-OPTION and used in the WHERE clause of SELECT statement, all the records for the corresponding key will be fetched.

Consider this code.


REPORT zztest_arun.

  TABLES : mara.
  DATA : it_mara_par TYPE STANDARD TABLE OF mara,
         it_mara_sel TYPE STANDARD TABLE OF mara,
         par_cnt TYPE i,
         sel_cnt TYPE i.

  PARAMETERS     : p_matnr TYPE mara-matnr.
  SELECT-OPTIONS : s_matnr FOR mara-matnr NO INTERVALS NO-EXTENSION.

*This option works same like PARAMETERS statement. The difference is that

*if no input value is passed in the selection screen no records are fetched

*for the PARAMETERS statement.... Whereas for SELECT-OPTIONS --- NO INTERVALS NO-EXTENSION.

*all the values will be fetched if no input is specified.START-OF-SELECTION.

*I am limiting the values to be fetched to 200 else it will take a lot of time.

**Dont give any values in the selecton screen and execute the program.

**No record will be fetched for PARAMETERS option


  SELECT * FROM mara INTO TABLE it_mara_par UP TO 200 ROWS WHERE matnr EQ p_matnr.
  DESCRIBE TABLE it_mara_par LINES par_cnt.

*All records in the table will be fetched for SELECT-OPTIONS --- NO INTERVALS NO-EXTENSION option

  SELECT * FROM mara INTO TABLE it_mara_sel UP TO 200 ROWS WHERE matnr IN s_matnr.

  DESCRIBE TABLE it_mara_sel LINES sel_cnt.

  WRITE : / 'No of Records Fetched when no data passed to Selection Screen',
  / 'PARAMETERS     :' , par_cnt,
  / 'SELECT-OPTIONS :' , sel_cnt.

Regards,

AS.