select where clause

[former_member599326]

I am using select statement as below...

select field1

          field2

          field3

           field4

from table

where field1 not in ('A','B','C');

In the above statement can I store the values 'A', 'B', 'C' in separate range outside abap program...????

in program we can use range statement that i know but again if we want to change the range values we have to change the program.

if we store this range values outside abap program then everytime we dont need to touch abap program if we want to add / remove range values.

Pls help

[former_member599326]

I am using select statement as below...

select field1

          field2

          field3

           field4

from table

where field1 not in ('A','B','C');

In the above statement can I store the values 'A', 'B', 'C' in separate range outside abap program...????

in program we can use range statement that i know but again if we want to change the range values we have to change the program.

if we store this range values outside abap program then everytime we dont need to touch abap program if we want to add / remove range values.

Pls help

[Former Member]

Hi Santosh,

Yes you can we generally do that in all projects... even SAP does that.

For boarder guideline Create a table as ZPARAM

Fields

PROGRAM_NAME

PARAM_NAME

VALUE1

VALUE2

Description

So for any program you need to maintain you can use it.

Select the required parameter and fill the range locally in your program

and use the range.

Cheers

[Former Member]

Hi Santosh,

Beside above solution, you may want to just make use of Variant, where ('A','B','C') is the input for a SELECT-OPTIONS.

select field1

          field2

          field3

           field4

from table

where field1 not in s_param.

So every time you want to change the input, you just need to change the Variant and save it.

Hope it helps. Cheers!

[Former Member]

Hi Santosh,

Yes you can store the range values outside your program. In SAP, we have a table called TVARVC, which is used to maintain constant values (both in parameter and select-options format) to be used in a program.

Parameter is used when only one value is to be maintained fro a single variable and select-options is used when multiple values are maintained for a single variable.

Follow the below steps to answer your query:

1) Go to transaction STVARV (this is used to create new TVARVC entries) --> Click on the "Select-Options" tab.

2) Click on the change button and Create new entry button in the application toolbar.

3) Give a variable name which is unique for your program.and click on the multiple selection button on the right hand side TO ENTER THE VALUES.

4) Now click on the save button.

5) Now go to your program and do the following:

TYPES:

*Type for TVARVC parameters

       BEGIN OF ty_tvarvc,

         name  TYPE rvari_vnam,  "Name

         sign  TYPE tvarv_sign,  "I/E (include/exclude values)

         opti  TYPE tvarv_opti,  "Selection option (EQ/BT/CP/...)

         low   TYPE tvarv_val,   "Selection value (LOW or HIGH value)

         high  TYPE tvarv_val,   "Selection value (LOW or HIGH value)

       END OF ty_tvarvc,

       tt_tvarvc TYPE STANDARD TABLE OF ty_tvarvc,

     tt_range_field1     type range of  auart.

CONSTANTS: lc_ztest_name  TYPE rvari_vnam VALUE 'ZTEST'.   "TVARVC Param. name

   DATA: lt_tvarvc TYPE tt_tvarvc, "Internal table for TVARVC values

              lt_field1     TYPE tt_range_field1,                "Range table for field field1

              ls_field1    TYPE LINE OF tt_range_field1.

   FIELD-SYMBOLS: <lfs_tvarvc> TYPE ty_tvarvc.

*Get the contents of TVARVC table

   SELECT name  "Name

          sign  "I/E (include/exclude values)

          opti  "Selection option (EQ/BT/CP/...)

          low   "Selection value (LOW or HIGH value)

          high  "Selection value (LOW or HIGH value)

   FROM tvarvc

   INTO TABLE lt_tvarvc

   WHERE name EQ lc_ztest_name.

IF sy-subrc IS INITIAL.

     LOOP AT lt_tvarvc ASSIGNING <lfs_tvarvc>.

       MOVE <lfs_tvarvc>-sign  TO ls_field1-sign.

       MOVE <lfs_tvarvc>-opti  TO ls_field1-option.

       MOVE <lfs_tvarvc>-low   TO ls_field1-low.

       MOVE <lfs_tvarvc>-high  TO ls_field1-high.

       APPEND ls_field1 TO lt_field1.

       CLEAR ls_auart.

     ENDLOOP.

   ENDIF.

6) Now your internal table LT_FIELD1 has become a range table containing the values which you maintained in the table TVARVC (outside of your program). This ensures that, in future if you need to change the constant values then you need to only change the TVARVC values for the corresponding variable name.

7) Now in your select statement you can directly use LT_FIELD1 as below:

select field1

          field2

          field3

           field4

from table

where field1 IN LT_FIELD1.

This is standard best practice to maintain constants outside your program to achieve better maintainability of the program and hence we use TVARVC table for this purpose.

Note: In the above coding example I have taken the range table type for  AUART. You can change it according to your requirement.

Hope this helps you.

Thanks,

Arnab

[Former Member]

Hi Santosh!

Your problem can be solved very simply:

   DATA:

     lt_mara            TYPE TABLE OF mara,

     lr_selection_range TYPE RANGE OF matnr,

     ls_selection       LIKE LINE OF lr_selection_range.

   " pre-populate the initial selection range - these values can also be read from a table

   ls_selection-low = '10170310'.

   APPEND ls_selection TO lr_selection_range.

   ls_selection-low = '10170311'.

   APPEND ls_selection TO lr_selection_range.

   ls_selection-low = '10170312'.

   APPEND ls_selection TO lr_selection_range.

   " construct IN selection criteria from pre-populated range

   LOOP AT lr_selection_range INTO ls_selection.

     " INTERNAL material number conversion - MARA specific

     CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT'

       EXPORTING

         input  = ls_selection-low

       IMPORTING

         OUTPUT = ls_selection-low.

     " prepare range entry as simple equals-statement

     ls_selection-sign   = 'I'.

     ls_selection-option = 'EQ'.

     MODIFY lr_selection_range FROM ls_selection.

   ENDLOOP.

   " perform actual selection

   SELECT *

     FROM mara

     INTO TABLE lt_mara

     WHERE matnr IN lr_selection_range.

What the above code does is to select three pre-defined materials from MARA.

While this example uses hard-coded material numbers you could also retrieve them - as stated earlier in this thread - from a custom table or any other source.

Please keep in mind that custom tables could contain the external representation of key values such as the material number since they are often maintained by business rather than technical users (to illustrate this I have included the material number conversion).

Regards,

  Chris