topic Re: problem in select query 1 in Application Development and Automation Discussions

problem in select query 1

Former Member — Tue, 02 Feb 2010 11:32:12 GMT

&----

*& Report ZPP_REPORT

&----

REPORT zpp_report_new NO STANDARD PAGE HEADING LINE-SIZE 255.

*******************************************************

DATA: p TYPE c LENGTH 50.

DO 5 TIMES.

WAIT UP TO '0.3' SECONDS.

p(12) = 'PROCESSING'.

p+12 = ': Made by Saurabh Srivastava (IT)'.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

percentage = sy-index

text = p.

ENDDO.

*******************************************************

TYPE-POOLS : slis.

TABLES : makt, mast, stpo, mard, afko, resb, marc.

DATA : BEGIN OF it_itab OCCURS 0,

aufnr LIKE afko-aufnr,

rsnum LIKE afko-rsnum,

END OF it_itab.

DATA : BEGIN OF it_itab2 OCCURS 0,

matnr LIKE mard-matnr, "Material

werks LIKE mard-werks,

lgort LIKE mard-lgort, " Storage Loc

labst LIKE mard-labst,

maktx LIKE makt-maktx,

END OF it_itab2.

TYPES : BEGIN OF itab1,

matnr LIKE resb-matnr, "Material

posnr LIKE resb-posnr,

stlnr LIKE resb-stlnr,

stlkn LIKE resb-stlkn,

rgekz LIKE resb-rgekz,

ewahr like resb-ewahr,
alpgr like resb-alpgr,
alpos like resb-alpos,

lgort LIKE stpo-lgort, " Storage Loc

menge LIKE stpo-menge, " Qty

req_qty LIKE stpo-menge,

labst LIKE mard-labst,

labst1 LIKE mard-labst,

labst2 LIKE mard-labst,

bal_stk LIKE mard-labst,

shrtg LIKE mard-labst,

qty LIKE stpo-menge, " Quantity

maktx LIKE makt-maktx, " Description

RGEKZ like marc-RGEKZ,

END OF itab1.

DATA : itab TYPE STANDARD TABLE OF itab1 WITH HEADER LINE.

DATA : itab2 TYPE STANDARD TABLE OF itab1 WITH HEADER LINE.

DATA : itab3 TYPE STANDARD TABLE OF itab1 WITH HEADER LINE.

DATA :col_pos TYPE i.

DATA : wa_itab TYPE itab1.

DATA : wa_itab3 TYPE itab1.

DATA : wa_it TYPE itab1.

DATA : wa_it1 TYPE itab1.

**----

ALV DECLERATION -

DATA: linecolor TYPE slis_specialcol_alv OCCURS 0 WITH HEADER LINE.

DATA: alv_layout TYPE slis_layout_alv.

DATA: alv_layout1 TYPE slis_layout_alv.

DATA: fieldcat TYPE slis_t_fieldcat_alv,

fieldcat_ln LIKE LINE OF fieldcat,

sortcat TYPE slis_t_sortinfo_alv,

sortcat_ln LIKE LINE OF sortcat,

eventcat TYPE slis_t_event,

eventcat_ln LIKE LINE OF eventcat,

alv_print TYPE slis_print_alv.

********************

DATA: fieldcat1 TYPE slis_t_fieldcat_alv,

fieldcat_ln1 LIKE LINE OF fieldcat,

sortcat1 TYPE slis_t_sortinfo_alv,

sortcat_ln1 LIKE LINE OF sortcat,

eventcat1 TYPE slis_t_event,

eventcat_ln1 LIKE LINE OF eventcat,

alv_print1 TYPE slis_print_alv.

DATA it_sort TYPE slis_t_sortinfo_alv.

DATA wa_sort LIKE LINE OF it_sort.

***----

SELECTION SCREEN -

SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS : matnr FOR mast-matnr OBLIGATORY NO INTERVALS NO-EXTENSION.

SELECT-OPTIONS : qty FOR stpo-menge OBLIGATORY NO INTERVALS NO-EXTENSION.

*SELECT-OPTIONS : lgort FOR stpo-lgort. " OBLIGATORY.

SELECTION-SCREEN : END OF BLOCK b1.

********************************************************************************

SELECT aufnr rsnum INTO TABLE it_itab FROM afko UP TO 1 ROWS WHERE plnbez IN matnr ORDER BY aufnr DESCENDING.

IF sy-subrc NE 0.

MESSAGE 'Data not found' TYPE 'E'.

ENDIF.

SELECT matnr posnr stlnr stlkn

*rgekz

FROM resb INTO TABLE itab FOR ALL ENTRIES IN it_itab WHERE rsnum = it_itab-rsnum AND erfmg NE '0.00'.

*********************************

LOOP AT itab.

SELECT SINGLE maktx FROM makt INTO itab-maktx WHERE matnr = itab-matnr.

SELECT SINGLE lgort menge FROM stpo INTO (itab-lgort , itab-menge) WHERE idnrk = itab-matnr AND stlnr = itab-stlnr AND stlkn = itab-stlkn.

SELECT SINGLE labst FROM mard INTO itab-labst WHERE matnr = itab-matnr AND werks = 'BILF' AND lgort = itab-lgort.

SELECT SINGLE rgekz FROM marc INTO itab-rgekz WHERE matnr = itab-matnr. " AND werks = 'BILF' AND lgort = itab-lgort.

SELECT SUM( labst ) FROM mard INTO itab-labst1 WHERE matnr = itab-matnr AND werks = 'BILF'

AND lgort IN ('COMP' , 'EBSS' , 'EDSS' , 'ELEC' , 'GDNS' , 'MAIN' , 'RECP' , 'SFNS' , 'COQA' , 'NSQA' , 'SPQA').

SELECT SUM( labst ) FROM mard INTO itab-labst2 WHERE matnr = itab-matnr AND werks = 'BILF'.

itab-bal_stk = itab-labst2 - itab-labst - itab-labst1.

IF itab-bal_stk LT 0.

itab-bal_stk = 0.

ENDIF.

itab-req_qty = qty-low * itab-menge.

itab-shrtg = itab-req_qty - itab-labst.

IF itab-shrtg LT 0.

itab-shrtg = 0.

ENDIF.

MODIFY itab INDEX sy-tabix TRANSPORTING maktx lgort req_qty labst rgekz labst1 shrtg bal_stk.

CLEAR : itab.

ENDLOOP.

********************************************************************************

PERFORM build_fieldcat1.

PERFORM build_fieldcat5.

PERFORM build_fieldcat2.

PERFORM build_fieldcat4.

PERFORM build_fieldcat3.

PERFORM build_fieldcat6.

PERFORM build_fieldcat7.

PERFORM build_fieldcat8.

PERFORM build_fieldcat9.

PERFORM build_fieldcat10.

PERFORM build_eventcat.

******PERFORM build_sortcat.

PERFORM start_list_viewer.

&----

*& Form START_LIST_VIEWER

&----

FORM start_list_viewer .

alv_layout-colwidth_optimize = 'X'.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = 'ZPP_REPORT_NEW'

i_callback_user_command = 'USER_COMMAND'

is_layout = alv_layout

it_fieldcat = fieldcat

i_default = 'X'

i_save = 'A'

it_sort = it_sort

TABLES

t_outtab = itab

EXCEPTIONS

program_error = 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.

ENDIF.

ENDFORM. " START_LIST_VIEWER

&----

*& Form BUILD_EVENTCAT

&----

FORM build_eventcat .

eventcat_ln-name = slis_ev_top_of_page.

eventcat_ln-form = 'TOP_OF_PAGE'.

APPEND eventcat_ln TO eventcat.

ENDFORM. " BUILD_EVENTCAT

&----

*& Form BUILD_SORTCAT

&----

***form BUILD_SORTCAT .

Clear: WA_SORT.
WA_SORT-spos = 1.
WA_SORT-fieldname = 'IDNRK'. "
WA_SORT-up = 'X'.
WA_SORT-subtot = 'X'.
append WA_SORT TO IT_SORT.

***endform. " BUILD_SORTCAT

&----

*& Form BUILD_FIELDCAT1

&----

FORM build_fieldcat1 .

ADD 1 TO col_pos.

fieldcat_ln-ref_tabname = 'ITAB'.

fieldcat_ln-fieldname = 'MATNR'.

fieldcat_ln-seltext_m = 'Material'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln-key = space.

fieldcat_ln-no_zero = space.

fieldcat_ln-do_sum = space.

fieldcat_ln-col_pos = col_pos.

fieldcat_ln-no_out = space.

fieldcat_ln-qfieldname = space.

fieldcat_ln-hotspot = space.

APPEND fieldcat_ln TO fieldcat.

ENDFORM. " BUILD_FIELDCAT1

&----

*& Form BUILD_FIELDCAT2

&----

FORM build_fieldcat2 .

ADD 1 TO col_pos.

fieldcat_ln-ref_tabname = 'ITAB'.

fieldcat_ln-fieldname = 'LGORT'.

fieldcat_ln-seltext_m = 'Stor Loc'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln-key = space.

fieldcat_ln-no_zero = space.

fieldcat_ln-do_sum = space.

fieldcat_ln-col_pos = col_pos.

fieldcat_ln-no_out = space.

fieldcat_ln-qfieldname = space.

fieldcat_ln-hotspot = space.

APPEND fieldcat_ln TO fieldcat.

ENDFORM. " BUILD_FIELDCAT2

&----

*& Form BUILD_FIELDCAT3

&----

FORM build_fieldcat3 .

ADD 1 TO col_pos.

fieldcat_ln-ref_tabname = 'ITAB'.

fieldcat_ln-fieldname = 'REQ_QTY'.

fieldcat_ln-seltext_m = 'Req Qty'.

fieldcat_ln-outputlen = '10'.

fieldcat_ln-datatype = 'QUAN'.

fieldcat_ln-key = space.

fieldcat_ln-no_zero = space.

fieldcat_ln-do_sum = space.

fieldcat_ln-col_pos = col_pos.

fieldcat_ln-no_out = space.

fieldcat_ln-qfieldname = space.

fieldcat_ln-hotspot = space.

APPEND fieldcat_ln TO fieldcat.

ENDFORM. " BUILD_FIELDCAT3

&----

*& Form BUILD_FIELDCAT4

&----

FORM build_fieldcat4 .

ADD 1 TO col_pos.

fieldcat_ln-ref_tabname = 'ITAB'.

fieldcat_ln-fieldname = 'POSNR'.

fieldcat_ln-seltext_m = 'Item No.'.

fieldcat_ln-outputlen = '18'.

Re: problem in select query 1

Former Member — Tue, 02 Feb 2010 11:33:02 GMT

continue....

**********************

fieldcat_ln-key = space.

fieldcat_ln-no_zero = space.

fieldcat_ln-do_sum = space.

fieldcat_ln-col_pos = col_pos.

fieldcat_ln-no_out = space.

fieldcat_ln-qfieldname = space.

fieldcat_ln-hotspot = space.

APPEND fieldcat_ln TO fieldcat.

ENDFORM. " BUILD_FIELDCAT4

&----

*& Form BUILD_FIELDCAT5

&----

FORM build_fieldcat5 .

ADD 1 TO col_pos.

fieldcat_ln-ref_tabname = 'ITAB'.

fieldcat_ln-fieldname = 'MAKTX'.

fieldcat_ln-seltext_m = 'Description'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln-key = space.

fieldcat_ln-no_zero = space.

fieldcat_ln-do_sum = space.

fieldcat_ln-col_pos = col_pos.

fieldcat_ln-no_out = space.

fieldcat_ln-qfieldname = space.

fieldcat_ln-hotspot = space.

APPEND fieldcat_ln TO fieldcat.

ENDFORM. " BUILD_FIELDCAT5

&----

*& Form BUILD_FIELDCAT6

&----

FORM build_fieldcat6 .

ADD 1 TO col_pos.

fieldcat_ln-ref_tabname = 'ITAB'.

fieldcat_ln-fieldname = 'LABST'.

fieldcat_ln-seltext_m = 'Conf Stk'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln-key = space.

fieldcat_ln-no_zero = space.

fieldcat_ln-do_sum = space.

fieldcat_ln-col_pos = col_pos.

fieldcat_ln-no_out = space.

fieldcat_ln-qfieldname = space.

fieldcat_ln-hotspot = space.

APPEND fieldcat_ln TO fieldcat.

ENDFORM. " BUILD_FIELDCAT6

&----

*& Form BUILD_FIELDCAT7

&----

FORM build_fieldcat7 .

ADD 1 TO col_pos.

fieldcat_ln-ref_tabname = 'ITAB'.

fieldcat_ln-fieldname = 'SHRTG'.

fieldcat_ln-seltext_m = 'Shortage'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln-key = space.

fieldcat_ln-no_zero = space.

fieldcat_ln-do_sum = space.

fieldcat_ln-col_pos = col_pos.

fieldcat_ln-no_out = space.

fieldcat_ln-qfieldname = space.

fieldcat_ln-hotspot = space.

APPEND fieldcat_ln TO fieldcat.

ENDFORM. " BUILD_FIELDCAT7

&----

*& Form BUILD_FIELDCAT8

&----

FORM build_fieldcat8 .

ADD 1 TO col_pos.

fieldcat_ln-ref_tabname = 'ITAB'.

fieldcat_ln-fieldname = 'LABST1'.

fieldcat_ln-seltext_m = 'Store Stk'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln-key = space.

fieldcat_ln-no_zero = space.

fieldcat_ln-do_sum = space.

fieldcat_ln-col_pos = col_pos.

fieldcat_ln-no_out = space.

fieldcat_ln-qfieldname = space.

fieldcat_ln-hotspot = space.

APPEND fieldcat_ln TO fieldcat.

ENDFORM. " BUILD_FIELDCAT8

&----

*& Form BUILD_FIELDCAT9

&----

FORM build_fieldcat9 .

ADD 1 TO col_pos.

fieldcat_ln-ref_tabname = 'ITAB'.

fieldcat_ln-fieldname = 'BAL_STK'.

fieldcat_ln-seltext_m = 'Bal.Prd Stk'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln-key = space.

fieldcat_ln-no_zero = space.

fieldcat_ln-do_sum = space.

fieldcat_ln-col_pos = col_pos.

fieldcat_ln-no_out = space.

fieldcat_ln-qfieldname = space.

fieldcat_ln-hotspot = space.

APPEND fieldcat_ln TO fieldcat.

ENDFORM. " BUILD_FIELDCAT9

*********************

FORM user_command USING r_ucomm LIKE sy-ucomm

rs_selfield TYPE slis_selfield.

CASE r_ucomm.

WHEN '&IC1'. "doubleclick

READ TABLE itab INTO wa_itab3 INDEX rs_selfield-tabindex.

IF sy-subrc = 0.

SELECT m1_{matnr m1}werks m1_{lgort m1}labst m2~maktx INTO TABLE it_itab2 FROM mard AS m1 INNER JOIN makt AS m2

ON m1_{matnr = m2}matnr

WHERE m1~matnr = wa_itab3-matnr

AND m1~werks = 'BILF'.

PERFORM build_fieldcat11.

PERFORM build_fieldcat55.

PERFORM build_fieldcat22.

PERFORM build_fieldcat66.

PERFORM build_eventcat1.

PERFORM start_list_viewer1.

ENDIF.

CLEAR r_ucomm.

REFRESH fieldcat1.

ENDCASE.

ENDFORM. "user_command

*************************

FORM start_list_viewer1 .

alv_layout1-colwidth_optimize = 'X'.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = 'ZPP_REPORT_NEW'

i_callback_user_command = 'USER_COMMAND'

is_layout = alv_layout1

it_fieldcat = fieldcat1

i_default = 'X'

i_save = 'A'

it_sort = it_sort

TABLES

t_outtab = it_itab2

EXCEPTIONS

program_error = 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.

ENDIF.

ENDFORM. " START_LIST_VIEWER

***************************************************

FORM build_eventcat1 .

eventcat_ln1-name = slis_ev_top_of_page.

eventcat_ln1-form = 'TOP_OF_PAGE'.

APPEND eventcat_ln1 TO eventcat1.

ENDFORM. " BUILD_EVENTCAT

********************************************

FORM build_fieldcat11 .

ADD 1 TO col_pos.

fieldcat_ln1-ref_tabname = 'IT_ITAB2'.

fieldcat_ln1-fieldname = 'MATNR'.

fieldcat_ln1-seltext_m = 'Material'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln1-key = space.

fieldcat_ln1-no_zero = space.

fieldcat_ln1-do_sum = space.

fieldcat_ln1-col_pos = col_pos.

fieldcat_ln1-no_out = space.

fieldcat_ln1-qfieldname = space.

fieldcat_ln1-hotspot = space.

APPEND fieldcat_ln1 TO fieldcat1.

ENDFORM. " BUILD_FIELDCAT1

**************************

FORM build_fieldcat55 .

ADD 1 TO col_pos.

fieldcat_ln1-ref_tabname = 'IT_ITAB2'.

fieldcat_ln1-fieldname = 'MAKTX'.

fieldcat_ln1-seltext_m = 'Description'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln1-key = space.

fieldcat_ln1-no_zero = space.

fieldcat_ln1-do_sum = space.

fieldcat_ln1-col_pos = col_pos.

fieldcat_ln1-no_out = space.

fieldcat_ln1-qfieldname = space.

fieldcat_ln1-hotspot = space.

APPEND fieldcat_ln1 TO fieldcat1.

ENDFORM. " BUILD_FIELDCAT5

**********************************************

FORM build_fieldcat66 .

ADD 1 TO col_pos.

fieldcat_ln1-ref_tabname = 'IT_ITAB2'.

fieldcat_ln1-fieldname = 'LABST'.

fieldcat_ln1-seltext_m = 'S.Loc Stk'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln1-key = space.

fieldcat_ln1-no_zero = space.

fieldcat_ln1-do_sum = space.

fieldcat_ln1-col_pos = col_pos.

fieldcat_ln1-no_out = space.

fieldcat_ln1-qfieldname = space.

fieldcat_ln1-hotspot = space.

APPEND fieldcat_ln1 TO fieldcat1.

ENDFORM. " BUILD_FIELDCAT6

****************

FORM build_fieldcat22 .

ADD 1 TO col_pos.

fieldcat_ln1-ref_tabname = 'IT_ITAB2'.

fieldcat_ln1-fieldname = 'LGORT'.

fieldcat_ln1-seltext_m = 'S.Loc'.

fieldcat_ln-outputlen = '18'.

fieldcat_ln1-key = space.

fieldcat_ln1-no_zero = space.

fieldcat_ln1-do_sum = space.

fieldcat_ln1-col_pos = col_pos.

fieldcat_ln1-no_out = space.

fieldcat_ln1-qfieldname = space.

fieldcat_ln1-hotspot = space.

APPEND fieldcat_ln1 TO fieldcat1.

ENDFORM. " BUILD_FIELDCAT6

&----

*& Form BUILD_FIELDCAT10

&----

FORM build_fieldcat10 .

ADD 1 TO col_pos.

fieldcat_ln-ref_tabname = 'ITAB'.

fieldcat_ln-fieldname = 'RGEKZ'.

fieldcat_ln-seltext_m = 'Back Flush'.

fieldcat_ln-outputlen = '10'.

fieldcat_ln-datatype = 'QUAN'.

fieldcat_ln-key = space.

fieldcat_ln-no_zero = space.

fieldcat_ln-do_sum = space.

fieldcat_ln-col_pos = col_pos.

fieldcat_ln-no_out = space.

fieldcat_ln-qfieldname = space.

fieldcat_ln-hotspot = space.

APPEND fieldcat_ln TO fieldcat.

ENDFORM. " BUILD_FIELDCAT10

Re: problem in select query 1

former_member217544 — Tue, 02 Feb 2010 11:36:44 GMT

Hi,

Do you think anyone can interpret this?

Either separate the code into blocks or post only the relevant part of code.

Regards,

Swarna Munukoti

Re: problem in select query 1

faisalatsap — Tue, 02 Feb 2010 11:37:22 GMT

there is a limit of 2500 characters per post. You don't know ?

Re: problem in select query 1

Former Member — Tue, 02 Feb 2010 11:38:00 GMT

Hi,

Can you write down just the query in which you are facing problem?

Thansk,

Archana