12-12-2013 2:35 AM
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.
12-12-2013 5:26 AM
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
12-12-2013 2:53 AM
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
12-12-2013 3:53 AM
Thx for ur respon Zhang (Archer).
Ya, i want extract *701 and *100 from my DB. Could u tell me the next step?
12-12-2013 5:27 AM
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
12-12-2013 5:26 AM
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