topic Query table using select-option in Application Development and Automation Discussions

Query table using select-option

Former Member — Thu, 12 Dec 2013 02:35:47 GMT

Dear ABAP lover,

Please help, i want query database table using where the data in select-option. The situasion like this:

s_matnr for mara-matnr

s_mtart for mara-mtar

s_werks for marc-werks.

I want select from table MARA and MARC using inner join with where condition:

select........

where a~matnr in s_matnr

b~werks in s_werks.

but s_werks is only the three last digit. For example i input : 3701, 4100 in plant select-option. So, just take 701 and 100 for value in s_werks.

Please any one give me tricks to do this.

Re: Query table using select-option

Former Member — Thu, 12 Dec 2013 02:53:43 GMT

hi Hendri.

if only process select-option, it`s simple.you can define a other range,

ranges: s_werks2 for marc-werks.

then, loop s_werks.

s_werks2-sign = s_werks-sign.

s_werks2-option = s_werks-option.

s_werks2-low = s_werks-low+1(3).

s_werks2-high = s_werks-high+1(3).

append s_werks2.

endloop.

using s_werks in your opensql.

But, what` you want from DB, only extract data 701 and 100,

or extract *701 and *100, i meaning 1701,2701,3701....1100,2100,3100...???

regards,

Archer

Re: Query table using select-option

Former Member — Thu, 12 Dec 2013 03:53:33 GMT

Thx for ur respon Zhang (Archer).

Ya, i want extract *701 and *100 from my DB. Could u tell me the next step?

Re: Query table using select-option

Former Member — Thu, 12 Dec 2013 05:26:11 GMT

Hi,

Please check the below code. I have done it for a single field.

To check the output use debugger.

TABLES : mara,marc.

SELECT-OPTIONS s_werks for marc-werks.

ranges: s_werks2 for marc-werks,

r_werks for marc-werks.

TYPES: BEGIN OF ty_rng, "Range Table

sign TYPE char01,

option TYPE char02,

low TYPE char4,

high TYPE char4,

END OF ty_rng.

DATA : wa_rng TYPE ty_rng,

itab_low TYPE TABLE OF marc,

itab_high TYPE TABLE OF marc,

itab TYPE TABLE OF marc.

AT SELECTION-SCREEN.

s_werks2-sign = s_werks-sign.

s_werks2-option = s_werks-option.

CONCATENATE '*' S_WERKS-LOW+1(3) INTO s_werks2-low.

CONCATENATE '*' S_WERKS-high+1(3) INTO s_werks2-high.

append s_werks2.

START-OF-SELECTION.

LOOP AT s_werks2 INTO wa_rng.

REPLACE ALL OCCURRENCES OF '*' IN wa_rng-low WITH '%'.

REPLACE ALL OCCURRENCES OF '*' IN wa_rng-high WITH '%'.

SELECT * from marc INTO TABLE itab_low

WHERE werks LIKE wa_rng-low.

SELECT * from marc INTO TABLE itab_high

WHERE werks LIKE wa_rng-high.

APPEND LINES OF itab_low to itab.

APPEND LINES OF itab_high to itab.

ENDLOOP.

WRITE : 'Hello'.

It can be helpful.

Regards

Re: Query table using select-option

Former Member — Thu, 12 Dec 2013 05:27:09 GMT

Hi Hendri,

In that case try something like this.

data: lv_low TYPE char20,

lv_high TYPE char20.

ranges: s_werks2 for marc-werks.

CONCATENATE '*' s_ptyp-low+4(3) INTO lv_low.

IF s_ptyp-high IS NOT INITIAL.

CONCATENATE '*' s_ptyp-high+5(3) INTO lv_high.

ENDIF.

loop at s_werks.

s_werks2-sign = 'I'.

s_werks2-option = 'CP'.

s_werks2-low = lv_low.

s_werks2-high = lv_high.

append s_werks2.

endloop.

and in the query use werks IN s_werks2.

Refer this thread also.

abap query to select a line item # from bseg on... | SCN

Hope this will work for you.

Regards

Anoop