topic Re: select where clause in Application Development and Automation Discussions

select where clause

former_member599326 — Sat, 20 Jul 2013 12:08:28 GMT

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

Re: select where clause

Former Member — Mon, 22 Jul 2013 03:45:57 GMT

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

Re: select where clause

Former Member — Mon, 22 Jul 2013 04:07:50 GMT

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!

Re: select where clause

Former Member — Mon, 22 Jul 2013 05:15:46 GMT

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

Re: select where clause

Former Member — Mon, 22 Jul 2013 06:14:12 GMT

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