03-12-2013 8:45 AM
Hi all ,
I m writing a program in which i have to fetch material from table mbewh with its total stock at any yrs period = 12 .code is as follow
tables : mbewh ,
mbew .
data : begin of itab occurs 100 ,
matnr like mbewh-matnr ,
bwkey like mbewh-bwkey ,
bwtar like mbewh-bwtar ,
lfgja like mbewh-lfgja ,
lfmon like mbewh-lfmon ,
lbkum LIKE mbewh-lbkum ,
total like mbewh-lbkum ,
end of itab .
data : begin of itab1 occurs 100 ,
matnr like mbewh-matnr ,
lbkum like mbewh-lbkum ,
end of itab1 .
DATA : RETURNCODE LIKE SY-SUBRC ,
field(40) type c .
data : BEGIN OF IHEADER OCCURS 10 ,
field_name(25) TYPE C,
END OF IHEADER.
refresh iheader .
clear iheader .
IHEADER-field_name = 'Material number'.
append iheader.
IHEADER-field_name = 'Plant'.
append iheader.
IHEADER-field_name = 'valuation '.
append iheader.
IHEADER-field_name = 'Year '.
append iheader.
"IHEADER-field_name = 'plant'.
"append iheader.
IHEADER-field_name = 'Month'.
append iheader.
IHEADER-field_name = 'Stock'.
append iheader.
IHEADER-field_name = 'Total_stock'.
append iheader.
select-options : matnr for mbewh-matnr ,
bwkey for mbewh-bwkey ,
bwtar for mbewh-bwtar .
parameters : lfgja like mbewh-lfgja OBLIGATORY.
refresh itab .
clear itab .
refresh itab .
clear itab .
select matnr bwkey bwtar lfgja lfmon lbkum sum( lbkum )
INTO (itab-matnr , itab-bwkey , itab-bwtar , itab-lfgja , itab-lfmon , itab-lbkum , itab-total )
FROM mbewh
WHERE matnr in matnr AND
bwkey in bwkey AND
lfgja = lfgja and
lfmon = '12'
group by (matnr)
.
append itab .
endselect .
sort itab .
CALL FUNCTION 'HR_DISPLAY_BASIC_LIST'
EXPORTING
BASIC_LIST_TITLE = SY-TITLE "JRFPH0K009969
FILE_NAME = SPACE
* HEAD_LINE1 = SY-TITLE "JRFPH0K009969
* HEAD_LINE1 = 'Employee Promotion Report'
" HEAD_LINE2 = page_head
* CURRENT_REPORT = SY-REPID
IMPORTING
RETURN_CODE = RETURNCODE
TABLES
DATA_TAB = itab
FIELDNAME_TAB = iheader
* SELECT_TAB = LIST_TAB_OUT
EXCEPTIONS
DOWNLOAD_PROBLEM = 1
OTHERS = 2 .
IF SY-SUBRC <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
write : ' No Data Is Present Or You Havenot Entered The Period ' .
ENDIF.
It is not giving me any compile time error when i m activating prog but when i try to run it gave me runtime errror
error : dbif_rsql_sql_error
exception : cx_sy_open_sql_db .
line which prompt error is :
select matnr bwkey bwtar lfgja lfmon lbkum sum( lbkum )
i need to find the total stock of all material (ie sum of stock of material in all plant )
please help me with it
.
Thanks in advance ,
shikha
03-12-2013 9:14 AM
Hi Sikha,
As during select statement you are applying SUM keyword so only you are getting the run time error.
Better you can do like this: -
1. Get all your materials and its stocks for the specific period into one internal table.
2. Use control breaks statement as below:
Loop At itab into wa.
At Last.
SUM.
" here move your stocks to another field which will be in the final table.
Endloop.
I hope it will work out.
Regards,
Gourav
03-12-2013 10:32 AM
Your SELECT statement is not correct, selected fields must either be part of the GROUP option or aggregate functions.
So replace
select matnr bwkey bwtar lfgja lfmon lbkum sum( lbkum ) group by (matnr).
with a correct statement like
select matnr sum( lbkum ) group by (matnr).
or
select matnr bwkey bwtar lfgja lfmon sum( lbkum ) group by (matnr bwkey bwtar lfgja lfmon).
Regards,
Raymond
03-12-2013 11:25 AM
Dear Raymond,
You are saying my select statement is not correct? Or the previous one?
03-12-2013 9:30 AM
Dear Sikha,
Please find my code as below:
*&---------------------------------------------------------------------*
*& Report ZGJ_TEST
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zgj_test.
TABLES : mbewh ,
mbew .
TYPES : BEGIN OF itab ,
matnr LIKE mbewh-matnr ,
bwkey LIKE mbewh-bwkey ,
bwtar LIKE mbewh-bwtar ,
lfgja LIKE mbewh-lfgja ,
lfmon LIKE mbewh-lfmon ,
lbkum LIKE mbewh-lbkum ,
END OF itab .
DATA: it TYPE TABLE OF itab,
wa TYPE itab.
DATA: it_final TYPE TABLE OF itab,
wa_final TYPE itab.
*data : begin of itab1 occurs 100 ,
* matnr like mbewh-matnr ,
* lbkum like mbewh-lbkum ,
* end of itab1 .
DATA : returncode LIKE sy-subrc ,
field(40) TYPE c .
DATA : BEGIN OF iheader OCCURS 10 ,
field_name(25) TYPE c,
END OF iheader.
REFRESH iheader .
CLEAR iheader .
iheader-field_name = 'Material number'.
APPEND iheader.
iheader-field_name = 'Plant'.
APPEND iheader.
iheader-field_name = 'valuation '.
APPEND iheader.
iheader-field_name = 'Year '.
APPEND iheader.
"IHEADER-field_name = 'plant'.
"append iheader.
iheader-field_name = 'Month'.
APPEND iheader.
iheader-field_name = 'Stock'.
APPEND iheader.
iheader-field_name = 'Total_stock'.
APPEND iheader.
SELECT-OPTIONS : matnr FOR mbewh-matnr ,
bwkey FOR mbewh-bwkey ,
bwtar FOR mbewh-bwtar .
PARAMETERS : lfgja LIKE mbewh-lfgja OBLIGATORY.
SELECT matnr bwkey bwtar lfgja lfmon lbkum INTO TABLE it
FROM mbewh WHERE matnr IN matnr AND
bwkey IN bwkey AND
lfgja = lfgja AND
lfmon = '12'.
DATA: lv_total TYPE mbewh-lbkum.
SORT it BY matnr.
LOOP AT it INTO wa.
wa_final-bwkey = wa-bwkey.
wa_final-bwtar = wa-bwtar.
wa_final-lfgja = wa-lfgja.
wa_final-lfmon = wa-lfmon.
wa_final-matnr = wa-matnr.
wa_final-lbkum = wa-lbkum.
APPEND wa_final TO it_final.
CLEAR: wa_final, wa.
ENDLOOP.
LOOP AT it_final INTO wa_final.
AT END OF matnr.
SUM.
wa_final-lbkum = wa_final-lbkum.
ENDAT.
ENDLOOP.
CALL FUNCTION 'HR_DISPLAY_BASIC_LIST'
EXPORTING
basic_list_title = sy-title "JRFPH0K009969
file_name = space
* HEAD_LINE1 = SY-TITLE "JRFPH0K009969
* HEAD_LINE1 = 'Employee Promotion Report'
" HEAD_LINE2 = page_head
* CURRENT_REPORT = SY-REPID
IMPORTING
return_code = returncode
TABLES
data_tab = it_final
fieldname_tab = iheader
* SELECT_TAB = LIST_TAB_OUT
EXCEPTIONS
download_problem = 1
OTHERS = 2.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
WRITE : ' No Data Is Present Or You Havenot Entered The Period ' .
ENDIF.
03-12-2013 11:39 AM
Dear Shikha,
I agree with in your case i think select should be written in below way:-
select matnr sum( lbkum )
INTO (itab-matnr , itab-total )
FROM mbewh
WHERE matnr in matnr AND
bwkey in bwkey AND
lfgja = lfgja and
lfmon = '12'
group by (matnr)
.
append itab .
endselect .
but Select Endselect should be avoided instead you can select all and use control break statements.
selecting all data in a internal table.
select matnr bwkey bwtar lfgja lfmon lbkum into table itab
from mbewh where matnr in matnr and
bwkey in bwkey and
lfgja = lfgja and
lfmon = '12'
loop at itab into wa_tab.
at end of matnr.
sum.
append wa_tab to itab2.
endat.
endloop.
Hope it helps you.
Thanks & Regards,
Vignesh Yeram
03-13-2013 10:11 AM
hi gorav ,
Ur report donot give desire result .
beside wat is purpose of this stmt .
wa_final-lbkum = wa_final-lbkum.
Thanks for the code
Regards ,
Shikha .
03-13-2013 10:56 AM
03-13-2013 11:07 AM
03-13-2013 11:22 AM
03-13-2013 11:44 AM
Dear Shikha,
I agree with Raymond Giuseppi in your case i think select should be written in below way:-
select matnr sum( lbkum )
INTO (itab-matnr , itab-total )
FROM mbewh
WHERE matnr in matnr AND
bwkey in bwkey AND
lfgja = lfgja and
lfmon = '12'
group by (matnr)
.
append itab .
endselect .
but Select Endselect should be avoided instead you can select all and use control break statements.
selecting all data in a internal table.
select matnr bwkey bwtar lfgja lfmon lbkum into table itab
from mbewh where matnr in matnr and
bwkey in bwkey and
lfgja = lfgja and
lfmon = '12'
loop at itab into wa_tab.
at end of matnr.
sum.
append wa_tab to itab2.
endat.
endloop.
Hope it helps you.
Thanks & Regards,
Vignesh Yeram