topic Re: Regarding select query in Application Development and Automation Discussions

Regarding select query

Former Member — Fri, 09 Jan 2009 05:48:53 GMT

Hi Experts,

Im using three 4 tables ekpo, eket, makt, mkpf. I developed for all entries using this tables.

SELECT EBELN

MATNR FROM EKPO INTO TABLE IT_EKPO WHERE EBELN IN Z_EBELN.

SELECT EBELN

EBELP

MENGE

EINDT

WEMNG

BEDAT

FROM EKET INTO CORRESPONDING FIELDS OF TABLE IT_EKET FOR ALL ENTRIES IN IT_EKPO

WHERE EBELN = IT_EKPO-EBELN.

SELECT MATNR

MAKTX FROM MAKT INTO CORRESPONDING FIELDS OF TABLE IT_MAKT FOR ALL ENTRIES IN IT_EKPO

WHERE MATNR = IT_EKPO-MATNR.

SELECT MBLNR

XBLNR FROM MKPF INTO CORRESPONDING FIELDS OF TABLE IT_MKPF FOR ALL ENTRIES IN IT_EKPO WHERE MATNR = IT_EKPO-MATNR.

Is this for all entries queries are executing perfectly?

how to append this data to ouput?

please provide any sample report using 6 tables

Re: Regarding select query

Former Member — Fri, 09 Jan 2009 06:14:00 GMT

hi,,

SELECT belnr

gjahr

bldat

budat

lifnr

kursf

waers

FROM rbkp

INTO TABLE i_rbkp

WHERE lifnr IN s_lifnr

AND budat IN s_budat.

SORT i_rbkp BY belnr.

IF sy-subrc = 0.

SELECT belnr

buzei

gjahr

matnr

ebeln

ebelp

menge

meins

wrbtr

tbtkz

shkzg

FROM rseg

INTO TABLE i_rseg

FOR ALL ENTRIES IN i_rbkp

WHERE belnr = i_rbkp-belnr

AND matnr IN s_matnr.

SORT i_rseg BY belnr .

ENDIF.

SELECT ebeln

bsart

wkurs

waers

FROM ekko

INTO TABLE i_ekko

FOR ALL ENTRIES IN i_rseg

WHERE ebeln = i_rseg-ebeln.

SORT i_ekko BY ebeln.

SELECT ebeln

netpr

infnr

matnr

ebelp

matkl

FROM ekpo

INTO TABLE i_ekpo

FOR ALL ENTRIES IN i_rseg

WHERE ebeln = i_rseg-ebeln

AND matkl IN s_matkl.

SELECT m~matnr

m~matkl

k~maktx

FROM mara

AS m

INNER JOIN makt

AS k

ON m_{matnr = k}matnr

INTO TABLE i_mara

WHERE matkl IN s_matkl

AND spras EQ 'E'

AND m~matnr IN s_matnr.

SELECT lifnr

name1

FROM lfa1

INTO TABLE i_lfa1

WHERE lifnr IN s_lifnr.

LOOP AT i_rseg INTO wa_rseg.

CLEAR: wa_rbkp, wa_mara,wa_ekpo,wa_ekko, wa_lfa1, wa_output.

MOVE-CORRESPONDING wa_rseg TO wa_output.

READ TABLE i_rbkp INTO wa_rbkp WITH KEY belnr = wa_rseg-belnr

BINARY SEARCH.

IF sy-subrc = 0.

wa_output-waers = wa_rbkp-waers.

wa_output-budat = wa_rbkp-budat.

wa_output-lifnr = wa_rbkp-lifnr.

IF wa_rbkp-kursf < 0.

wa_rbkp-kursf = wa_rbkp-kursf * ( - 1 ).

ENDIF.

wa_output-kursf = wa_rbkp-kursf.

ENDIF.

READ TABLE i_lfa1 INTO wa_lfa1 WITH KEY lifnr = wa_rbkp-lifnr

BINARY SEARCH.

IF sy-subrc = 0.

wa_output-name1 = wa_lfa1-name1.

ENDIF.

IF wa_output-waers NE 'EUR'.

if wa_rbkp-kursf < 1.

wa_output-punitp = wa_rseg-wrbtr / wa_rbkp-kursf.

else.
wa_output-punitp = wa_rseg-wrbtr * wa_rbkp-kursf.
endif.

ELSE.

wa_output-punitp = wa_rseg-wrbtr.

ENDIF.

IF wa_rseg-tbtkz = 'X'.

IF wa_rseg-shkzg = 'S'.

MOVE 'SD' TO wa_output-shkzg.

ELSE.

MOVE 'SC' TO wa_output-shkzg.

CONCATENATE '-' wa_output-punitp INTO wa_output-punitp.

CONDENSE wa_output-punitp NO-GAPS.

ENDIF.

ELSE.

MOVE ' ' TO wa_output-shkzg.

ENDIF.

wa_output-punitpr = wa_rseg-wrbtr / wa_rseg-menge.

IF wa_rseg-tbtkz = 'X'.

IF wa_rseg-shkzg = 'S'.

MOVE 'SD' TO wa_output-shkzg.

ELSE.

MOVE 'SC' TO wa_output-shkzg.

CONCATENATE '-' wa_output-wrbtr INTO wa_output-wrbtr.

CONDENSE wa_output-wrbtr NO-GAPS.

ENDIF.

ELSE.

MOVE ' ' TO wa_output-shkzg.

ENDIF.

READ TABLE i_mara INTO wa_mara WITH KEY matnr = wa_rseg-matnr

BINARY SEARCH.

IF sy-subrc = 0.

wa_output-matkl = wa_mara-matkl.

wa_output-maktx = wa_mara-maktx.

ENDIF.

READ TABLE i_ekko INTO wa_ekko WITH KEY ebeln = wa_rseg-ebeln

BINARY SEARCH.

IF sy-subrc = 0.

wa_output-bsart = wa_ekko-bsart.

wa_output-waer = wa_ekko-waers.

ENDIF.

IF wa_ekko-wkurs < 0.

wa_ekko-wkurs = wa_ekko-wkurs * ( - 1 ).

ENDIF.

wa_output-wkurs = wa_ekko-wkurs.

READ TABLE i_ekpo INTO wa_ekpo WITH KEY matnr = wa_rseg-matnr ebeln = wa_rseg-ebeln.

IF sy-subrc = 0.

wa_output-netpr = wa_ekpo-netpr.

IF wa_output-waer NE 'EUR'.

wa_output-pnet = wa_output-netpr / wa_output-wkurs.

ELSE.

wa_output-pnet = wa_output-netpr.

ENDIF.

APPEND wa_output TO i_output.

ENDLOOP.

CLEAR wa_output.

LOOP AT i_output INTO wa_output WHERE matkl IN s_matkl.

APPEND wa_output TO i_output1.

ENDLOOP

Re: Regarding select query

Former Member — Fri, 09 Jan 2009 06:15:16 GMT

Loop at it_tab1 into wa_tab1.

wa_final-field1 = wa_tab1-field.

wa_final-field2 = wa_tab1-field.

.................

read table it_tab2 into wa_tab2 with key filed = wa_tab1-field. (key field of the tables)

if sy-subrc = 0.

wa_final-field3 = wa_tab2-field.

..................

read table it_tab3 into wa_tab3 with key filed = wa_tab1-field. (key field of the tables)

if sy-subrc = 0.

wa_final-field4 = wa_tab3-field.

..............

read table it_tab4 into wa_tab4 with key filed = wa_tab1-field. (key field of the tables)

if sy-subrc = 0.

wa_final-field5 = wa_tab4-field.

.....................

endif. endif. endif.

append wa_final to it_final.

endloop.

Re: Regarding select query

I355602 — Fri, 09 Jan 2009 06:15:33 GMT

Hi,

Reading data from 4 database table into a final internal table using 'FOR ALL ENTRIES'.

Use this code, its working:-


*&---------------------------------------------------------------------*
*          RANGES DECLARATION
*&---------------------------------------------------------------------*
*          RANGE VALUE TO BE USED FOR THE ORDER TYPE
*&---------------------------------------------------------------------*
RANGES : r_auart FOR aufk-auart. "for order type

*&---------------------------------------------------------------------*
*          TYPE DECLARATION
*&---------------------------------------------------------------------*
TYPES :
* for table aufk (work order details)
        BEGIN OF t_aufk,
          aufnr TYPE aufk-aufnr, " order number
          ktext TYPE aufk-ktext, " description
          objnr TYPE aufk-objnr, " object number
        END OF t_aufk,

* for table jest (object status details)
        BEGIN OF t_jest,
          objnr TYPE jest-objnr, " object number
        END OF t_jest,

* for table afko (basic finish date details)
        BEGIN OF t_afko,
          aufnr TYPE afko-aufnr, " order number
          gltrp TYPE afko-gltrp, "basic finish date
        END OF t_afko,

* for table qmel (notification number details)
        BEGIN OF t_qmel,
          aufnr TYPE qmel-aufnr, " order number
          qmnum TYPE qmel-qmnum, " notification number
        END OF t_qmel,

* final table from which the records need to be displayed
        BEGIN OF t_final,
          aufnr TYPE aufk-aufnr, " order number
          ktext TYPE aufk-ktext, " description
          qmnum TYPE qmel-qmnum, " notification number
          flag(1), " for selection of records
        END OF t_final.

*&---------------------------------------------------------------------*
*          INTERNAL TABLE & WORK AREA DECLARATIONS
*&---------------------------------------------------------------------*
* TYPE OF T_AUFK
* internal table to select records for damaged order types
DATA : it_aufk TYPE STANDARD TABLE OF t_aufk,
* work area for damaged order types
       wa_aufk TYPE t_aufk.

* TYPE OF T_JEST
* internal table to select object number based upon user status
DATA : it_jest TYPE STANDARD TABLE OF t_jest,
* work area to select object number based upon user status
       wa_jest TYPE t_jest.

* TYPE OF T_AFKO
* internal table to select the basic finish date corresopnding to work order
DATA : it_afko TYPE STANDARD TABLE OF t_afko,
* work area to select the basic finish date corresopnding to work order
       wa_afko TYPE t_afko.

* TYPE OF T_QMEL
* internal table to select the notification number corresopnding to object number
DATA : it_qmel TYPE STANDARD TABLE OF t_qmel,
* work area to select the notification number corresopnding to object number
       wa_qmel TYPE t_qmel.

* TYPE OF T_FINAL
* final internal table to select final records to be displayed
DATA : it_final TYPE STANDARD TABLE OF t_final,
* final work area to select final records to be displayed
       wa_final TYPE t_final.

*&---------------------------------------------------------------------*
*          INITIALIZATION
*&---------------------------------------------------------------------*
*          TO INITIALIZE ALL THE VARIABLES TO BE USED
*          IN THE PROGRAM
*&---------------------------------------------------------------------*
INITIALIZATION.
  r_auart-sign = 'I'.             " for inclusive
  r_auart-option = 'BT'.          " for between operator
  r_auart-low = 'PM01'.           " order type(low value)
  r_auart-high = 'PM02'.          " order type(high value)
  APPEND r_auart.                 " append values for range

*&---------------------------------------------------------------------*
*          START-OF-SELECTION
*&---------------------------------------------------------------------*
START-OF-SELECTION.

*&---------------------------------------------------------------------*
*          CLEAR ALL THE CONTENTS OF ALL INTERBAL TABLES
*&---------------------------------------------------------------------*
  REFRESH it_jest.
  REFRESH it_aufk.
  REFRESH it_afko.
  REFRESH it_qmel.
  REFRESH it_final.
*&---------------------------------------------------------------------*
*          select user status from TJ02T for TXT04(status of an object)
*          into variable V_ISTAT
*&---------------------------------------------------------------------*
  SELECT SINGLE
    istat
  FROM tj02t
  INTO (v_istat)
  WHERE
    txt04 EQ 'TECO' AND
    spras EQ 'E'.

*&---------------------------------------------------------------------*
*          select object number based upon the user status selected
*&---------------------------------------------------------------------*
  SELECT
    objnr
  FROM jest
  INTO TABLE it_jest
  WHERE
    stat EQ v_istat.
  IF sy-subrc <> 0.
    MESSAGE e003.
  ELSE.
*&---------------------------------------------------------------------*
*         select into it_aufk table: object number, description and
*         object number based on object number selected and order type
*         in PM01 and PM02
*&---------------------------------------------------------------------*
    SELECT
      aufnr
      ktext
      objnr
    FROM aufk
    INTO TABLE it_aufk
    FOR ALL ENTRIES IN it_jest
    WHERE
      objnr EQ it_jest-objnr AND
      auart IN r_auart.

    IF sy-subrc <> 0.
      MESSAGE e002.
    ELSE.
*&---------------------------------------------------------------------*
*         select into it_afko table: order number and basic finish date
*         based upon order number selected and basic finish date as
*         v_cdate variable
*&---------------------------------------------------------------------*
      SELECT
        aufnr
        gltrp
      FROM afko
      INTO TABLE it_afko
      FOR ALL ENTRIES IN it_aufk
      WHERE
        aufnr EQ it_aufk-aufnr AND
        gltrp LE sy-datum.
*&---------------------------------------------------------------------*
*         select into it_qmel table: order number and notification
*         number based on the order number selected
*&---------------------------------------------------------------------*
      SELECT
        aufnr
        qmnum
      FROM qmel
      INTO TABLE it_qmel
      FOR ALL ENTRIES IN it_aufk
      WHERE
        aufnr EQ it_aufk-aufnr.
    ENDIF.
  ENDIF.


*&---------------------------------------------------------------------*
*          READ THE RECORDS FOMR THE ABOVE POPULATED INTERNAL TABLE
*          INTO WORK AREA AND THEN POPULATE THE FINAL INTERNAL
*          TABLE
*&---------------------------------------------------------------------*
* read internal table it_jest into work area wa_jest
  LOOP AT it_jest INTO wa_jest.

* read it_aufk based on object number from wa_jest
    READ TABLE it_aufk INTO wa_aufk WITH KEY objnr = wa_jest-objnr.
* if records found
    IF sy-subrc EQ 0.
* read it_afko for order number and description based on order number
      READ TABLE it_afko INTO wa_afko WITH KEY aufnr = wa_aufk-aufnr.
* if records found
      IF sy-subrc EQ 0.
        wa_final-aufnr = wa_afko-aufnr. "move order number to it_final
        wa_final-ktext = wa_aufk-ktext. "move description to it_final
      ELSE.
* if no record found then move to next loop pass
        CONTINUE.
      ENDIF.
    ELSE.
* if no record found then move to next loop pass
      CONTINUE.
    ENDIF.

* read it_qmel for description based on the order number selected above
    READ TABLE it_qmel INTO wa_qmel WITH KEY aufnr = wa_final-aufnr.
* if records found
    IF sy-subrc EQ 0.
      wa_final-qmnum = wa_qmel-qmnum. "move notification number to it_final
    ENDIF.

* finally append the records into the final internal table
    APPEND wa_final TO it_final. "append wa_final into it_final
    CLEAR wa_final. "clear work area
    CLEAR wa_aufk.  "clear work area
    CLEAR wa_qmel.  "clear work area

  ENDLOOP.

Hope this helps you.

Thanks & Regards,

Tarun Gambhir

Re: Regarding select query

Former Member — Fri, 09 Jan 2009 06:17:12 GMT

hi,

SELECT EBELN

MATNR FROM EKPO INTO TABLE IT_EKPO WHERE EBELN IN Z_EBELN.

SELECT EBELN

EBELP

MENGE

EINDT

WEMNG

BEDAT

FROM EKET INTO CORRESPONDING FIELDS OF TABLE IT_EKET FOR ALL ENTRIES IN IT_EKPO

WHERE EBELN = IT_EKPO-EBELN.

SELECT MATNR

MAKTX FROM MAKT INTO CORRESPONDING FIELDS OF TABLE IT_MAKT FOR ALL ENTRIES IN IT_EKPO

WHERE MATNR = IT_EKPO-MATNR.

SELECT MBLNR

XBLNR FROM MKPF INTO CORRESPONDING FIELDS OF TABLE IT_MKPF FOR ALL ENTRIES IN IT_EKPO WHERE MATNR = IT_EKPO-MATNR.

ya your is working fine...

Re: Regarding select query

Former Member — Fri, 09 Jan 2009 06:19:13 GMT

Hi,

When ever you are using FOR ALL ENTRIES better fetch the primary key fields and then the remaining fields.

So that all the entries will come from the data base. other wise duplicate records will not come.

I Mean in your select for eket you are fetching

EBELN

EBELP

MENGE

EINDT

WEMNG

BEDAT

in this you are missing the key field ETENR. So you will not all the records from eket.

Whether it is required for you or not i am not sure.

Now take it_ekpo as the base table to fill the output table.

Loop at it_ekpo.

read the table IT_EKET with key EBELN = IT_EKPO-EBELN.

then read IT_MAKT with key matnr = it_ekpo-matnr

then read IT_MKPF with key matnr = it_ekpo-matnr.

You should have a criteria to fill the final internal table or modify the it_ekpo table with the details you want.

endloop.

Regards,

Venkatesh

Re: Regarding select query

Former Member — Fri, 09 Jan 2009 06:20:31 GMT

Hi,

Before each query using FOR ALL ENTRIES, check for IT_EKPO if its initial. i.e.

SELECT EBELN

MATNR FROM EKPO INTO TABLE IT_EKPO WHERE EBELN IN Z_EBELN.

If IT_EKPO[] is not initial.

SELECT EBELN

EBELP

MENGE

EINDT

WEMNG

BEDAT

FROM EKET INTO CORRESPONDING FIELDS OF TABLE IT_EKET FOR ALL ENTRIES IN IT_EKPO

WHERE EBELN = IT_EKPO-EBELN.

SELECT MATNR

MAKTX FROM MAKT INTO CORRESPONDING FIELDS OF TABLE IT_MAKT FOR ALL ENTRIES IN IT_EKPO

WHERE MATNR = IT_EKPO-MATNR.

SELECT MBLNR

XBLNR FROM MKPF INTO CORRESPONDING FIELDS OF TABLE IT_MKPF FOR ALL ENTRIES IN IT_EKPO WHERE MATNR = IT_EKPO-MATNR.

ENDIF.

For putting it into output,

Loop at IT_EKPO.

Loop at IT_EKET where ebeln = it_ekpo-ebeln.

IT_OUTPUT-EBELN = IT_EKPO-EBELN.

IT_OUTPUT-EBELP = IT_EKPO-EBELP.

IT_OUTPUT-MENGE = IT_EKPO-MENGE.

IT_OUTPUT-EINDT = IT_EKPO-EINDT.

IT_OUTPUT-WEMNG = IT_EKPO-WEMNG.

IT_OUTPUT-BEDAT = IT_EKPO-BEDAT.

Loop at IT_MAKT where ebeln = it_ekpo-ebeln.

<move corresponding fields to output table>.

Loop at IT_MKPF where ebeln = it_ekpo-ebeln.

<move corresponding fields to output table>.

Append it_output.

clear it_output.

Endloop.

endloop.

Hope this will help you.

Regards

Natasha Garg

Re: Regarding select query

SujeetMishra — Fri, 09 Jan 2009 08:54:35 GMT

Hello.

Your code is Ok.only thing is you can append your required fields within LOOP.

loop at IT_EKPO into WA_EKPO.

wa_final-matnr = IT_EKPO-MATNR.

modify it_final from wa_final.

endloop.

and for output use final internal table .here this is it_final.

Regards,

Sujeet

Re: Regarding select query

Former Member — Fri, 09 Jan 2009 10:37:04 GMT

Hi Experts,

After some modification from my functional consultant, i modified code, but the data is not getting outputted.

This is my code,

REPORT YSAMPLEFORALLENTRIES002.

TABLES: EKPO, EKET, MAKT, EKBE.

TYPE-POOLS: SLIS.

TYPES: BEGIN OF ST_EKKO,

EBELN TYPE EKKO-EBELN,

LIFNR TYPE EKKO-LIFNR,

BEDAT TYPE EKKO-BEDAT,

END OF ST_EKKO.

TYPES: BEGIN OF ST_EKPO,

EBELN TYPE EKPO-EBELN,

EBELP TYPE EKPO-EBELP,

MATNR TYPE EKPO-MATNR,

END OF ST_EKPO.

TYPES: BEGIN OF ST_EKET,

EBELN TYPE EKET-EBELN,

EBELP TYPE EKET-EBELP,

ETENR TYPE EKET-ETENR,

MENGE TYPE EKET-MENGE,

EINDT TYPE EKET-EINDT,

WEMNG TYPE EKET-WEMNG,

BEDAT TYPE EKET-BEDAT,

END OF ST_EKET.

TYPES: BEGIN OF ST_MAKT,

MATNR TYPE MAKT-MATNR,

MAKTX TYPE MAKT-MAKTX,

END OF ST_MAKT.

TYPES: BEGIN OF ST_EKBE,

EBELN TYPE EKBE-EBELN,

XBLNR TYPE EKBE-XBLNR,

MATNR TYPE EKBE-MATNR,

END OF ST_EKBE.

TYPES: BEGIN OF ST_OUTPUT,

MATNR TYPE EKPO-MATNR,

MAKTX TYPE MAKT-MAKTX,

EBELP TYPE EKET-EBELP,

XBLNR TYPE EKBE-XBLNR,

MENGE TYPE EKET-MENGE,

EINDT TYPE EKET-EINDT,

WEMNG TYPE EKET-WEMNG,

END OF ST_OUTPUT.

DATA: IT_OUTPUT TYPE TABLE OF ST_OUTPUT WITH HEADER LINE,

WA_OUTPUT TYPE TABLE OF ST_OUTPUT WITH HEADER LINE.

DATA: IT_EKKO TYPE TABLE OF ST_EKKO,

WA_EKKO TYPE ST_EKKO.

DATA: IT_EKPO TYPE TABLE OF ST_EKPO,

WA_EKPO TYPE ST_EKPO.

DATA: IT_EKET TYPE TABLE OF ST_EKET,

WA_EKET TYPE ST_EKET.

DATA: IT_MAKT TYPE TABLE OF ST_MAKT,

WA_MAKT TYPE ST_MAKT.

DATA: IT_EKBE TYPE TABLE OF ST_EKBE,

WA_EKBE TYPE ST_EKBE.

DATA: S_EBELN TYPE EKKO-EBELN,

S_DATE TYPE EKKO-BEDAT.

SELECTION-SCREEN: BEGIN OF BLOCK B1.

PARAMETERS: Z_LIFNR typE EKKO-LIFNR.

SELECT-OPTIONS: Z_EBELN FOR S_EBELN,

ZST_DATE FOR S_DATE.

SELECTION-SCREEN: END OF BLOCK B1.

*at selection-screen.

*IF Z_LIFNR = ' ' AND Z_EBELN = ' '.

*PERFORM FETCH_DATA.

*MESSAGE E001(ZMSG) WITH 'DATA DOES NOT EXISTS'.

*ENDIF.

START-OF-SELECTION.

PERFORM GET_DATA.

PERFORM DISPLAY_DATA.

&----

*& Form GET_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM GET_DATA .

SELECT EBELN

LIFNR

BEDAT FROM EKKO INTO TABLE IT_EKKO WHERE EBELN IN Z_EBELN

AND LIFNR = Z_LIFNR

AND BEDAT IN ZST_DATE.

SELECT EBELN

EBELP

MATNR FROM EKPO INTO TABLE IT_EKPO WHERE EBELN IN Z_EBELN.

SELECT MATNR

MAKTX FROM MAKT INTO CORRESPONDING FIELDS OF TABLE IT_MAKT FOR ALL ENTRIES IN IT_EKPO

WHERE MATNR = IT_EKPO-MATNR.

SELECT EBELN

EBELP

ETENR

MENGE

EINDT

WEMNG

BEDAT

FROM EKET INTO CORRESPONDING FIELDS OF TABLE IT_EKET FOR ALL ENTRIES IN IT_EKPO

WHERE EBELN = IT_EKPO-EBELN.

SELECT EBELN

XBLNR

MATNR FROM EKBE INTO CORRESPONDING FIELDS OF TABLE IT_EKBE FOR ALL ENTRIES IN IT_EKBE

WHERE EBELN = IT_EKBE-EBELN.

LOOP AT IT_EKPO INTO WA_EKPO.

WA_OUTPUT-MATNR = WA_EKPO-MATNR.

READ TABLE IT_MAKT INTO WA_MAKT WITH KEY MATNR = WA_EKPO-MATNR BINARY SEARCH.

WA_OUTPUT-MAKTX = WA_MAKT-MAKTX.

READ TABLE IT_EKET INTO WA_EKET WITH KEY EBELN = WA_EKPO-EBELN BINARY SEARCH.

WA_OUTPUT-EBELP = WA_EKET-EBELP.

WA_OUTPUT-MENGE = WA_EKET-MENGE.

WA_OUTPUT-WEMNG = WA_EKET-WEMNG.

READ TABLE IT_EKBE INTO WA_EKBE WITH KEY EBELN = WA_EKPO-EBELN BINARY SEARCH.

WA_OUTPUT-XBLNR = WA_EKBE-XBLNR.

APPEND WA_OUTPUT TO IT_OUTPUT.

ENDLOOP.

ENDFORM. " GET_DATA

&----

*& Form DISPLAY_DATA

&----

text

----

--> p1 text
<-- p2 text

----

FORM DISPLAY_DATA .

DATA: IT_FIELDCAT TYPE SLIS_T_FIELDCAT_ALV WITH HEADER LINE.

*LOOP AT IT_OUTPUT INTO WA_OUTPUT. " WHERE EBELN IN Z_EBELN.

IT_FIELDCAT-COL_POS = 1.

IT_FIELDCAT-SELTEXT_L = 'Sl.No'.

APPEND IT_FIELDCAT.

IT_FIELDCAT-COL_POS = 2.

IT_FIELDCAT-SELTEXT_L = 'Part Number'.

IT_FIELDCAT-FIELDNAME = 'IT_OUTPUT-MATNR'.

IT_FIELDCAT-TABNAME = 'IT_OUTPUT'.

APPEND IT_FIELDCAT.

IT_FIELDCAT-COL_POS = 3.

IT_FIELDCAT-SELTEXT_L = 'Description'.

IT_FIELDCAT-FIELDNAME = 'IT_OUTPUT-MAKTX'.

IT_FIELDCAT-TABNAME = 'IT_OUTPUT'.

APPEND IT_FIELDCAT.

IT_FIELDCAT-COL_POS = 4.

IT_FIELDCAT-SELTEXT_L = 'LINE ITEM'.

IT_FIELDCAT-FIELDNAME = 'IT_OUTPUT-EBELP'.

IT_FIELDCAT-TABNAME = 'IT_OUTPUT'.

APPEND IT_FIELDCAT.

IT_FIELDCAT-COL_POS = 5.

IT_FIELDCAT-SELTEXT_L = 'Backlog Quantity'.

IT_FIELDCAT-FIELDNAME = ''.

APPEND IT_FIELDCAT.

IT_FIELDCAT-COL_POS = 6.

IT_FIELDCAT-SELTEXT_L = 'Last Document Refernce'.

IT_FIELDCAT-FIELDNAME = 'IT_OUTPUT-XBLNR'.

IT_FIELDCAT-TABNAME = 'IT_OUTPUT'.

APPEND IT_FIELDCAT.

IT_FIELDCAT-COL_POS = 7.

IT_FIELDCAT-SELTEXT_L = 'Schedule Quantity'.

IT_FIELDCAT-FIELDNAME = 'IT_OUTPUT-MENGE'.

IT_FIELDCAT-TABNAME = 'IT_OUTPUT'.

APPEND IT_FIELDCAT.

IT_FIELDCAT-COL_POS = 8.

IT_FIELDCAT-SELTEXT_L = 'Date'.

IT_FIELDCAT-FIELDNAME = 'IT_OUTPUT-EINDT'.

IT_FIELDCAT-TABNAME = 'IT_OUTPUT'.

APPEND IT_FIELDCAT.

IT_FIELDCAT-COL_POS = 9.

IT_FIELDCAT-SELTEXT_L = 'Delivered Quantity'.

IT_FIELDCAT-FIELDNAME = 'IT_OUTPUT-WEMNG'.

IT_FIELDCAT-TABNAME = 'IT_OUTPUT'.

APPEND IT_FIELDCAT.

IT_FIELDCAT-COL_POS = 10.

IT_FIELDCAT-SELTEXT_L = 'BALANCE QUANTITY'.

APPEND IT_FIELDCAT.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_INTERFACE_CHECK = ' '
I_BYPASSING_BUFFER = ' '
I_BUFFER_ACTIVE = ' '

I_CALLBACK_PROGRAM = 'YSAMPLEFORALLENTRIES002'

I_CALLBACK_PF_STATUS_SET = ' '
I_CALLBACK_USER_COMMAND = ' '
I_CALLBACK_TOP_OF_PAGE = ' '
I_CALLBACK_HTML_TOP_OF_PAGE = ' '
I_CALLBACK_HTML_END_OF_LIST = ' '
I_STRUCTURE_NAME =
I_BACKGROUND_ID = ' '
I_GRID_TITLE =
I_GRID_SETTINGS =
IS_LAYOUT =

IT_FIELDCAT = IT_FIELDCAT[]

IT_EXCLUDING =
IT_SPECIAL_GROUPS =
IT_SORT =
IT_FILTER =
IS_SEL_HIDE =
I_DEFAULT = 'X'
I_SAVE = ' '
IS_VARIANT =
IT_EVENTS =
IT_EVENT_EXIT =
IS_PRINT =
IS_REPREP_ID =
I_SCREEN_START_COLUMN = 0
I_SCREEN_START_LINE = 0
I_SCREEN_END_COLUMN = 0
I_SCREEN_END_LINE = 0
I_HTML_HEIGHT_TOP = 0
I_HTML_HEIGHT_END = 0
IT_ALV_GRAPHICS =
IT_HYPERLINK =
IT_ADD_FIELDCAT =
IT_EXCEPT_QINFO =
IR_SALV_FULLSCREEN_ADAPTER =
IMPORTING
E_EXIT_CAUSED_BY_CALLER =
ES_EXIT_CAUSED_BY_USER =

TABLES

T_OUTTAB = WA_OUTPUT[]

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.

*ENDLOOP.

ENDFORM. " DISPLAY_DATA

please suggest me modifications.

Regards,

Bharat

Re: Regarding select query

Former Member — Fri, 09 Jan 2009 10:51:01 GMT

Hi Bharat ,

Please find the below For All enteries dummy program for help:

REPORT ztg_foral.

&--

FOR TABLE ZVBAK--

TYPES : BEGIN OF t_vbak,

vbeln TYPE zvbak-vbeln,

vkorg TYPE zvbak-vkorg,

vtweg TYPE zvbak-vtweg,

spart TYPE zvbak-spart,

END OF t_vbak.

&--

FOR TABLE ZMARA--

TYPES : BEGIN OF t_mara,

matnr TYPE zmara-matnr,

mbrsh TYPE zmara-mbrsh,

meins TYPE zmara-meins,

END OF t_mara.

&--

FOR TABLE ZVBAP--

TYPES : BEGIN OF t_vbap,

vbeln TYPE zvbap-vbeln,

posnr TYPE zvbap-posnr,

matnr TYPE zvbap-matnr,

matkl TYPE zvbap-matkl,

END OF t_vbap.

&----

FOR ALL THREE TABLES----

TYPES : BEGIN OF t_final,

vbeln TYPE zvbak-vbeln,

vkorg TYPE zvbak-vkorg,

vtweg TYPE zvbak-vtweg,

spart TYPE zvbak-spart,

posnr TYPE zvbap-posnr,

matnr TYPE zvbap-matnr,

matkl TYPE zvbap-matkl,

mbrsh TYPE zmara-mbrsh,

meins TYPE zmara-meins,

END OF t_final.

&--

FOR TABLE ZVBAK--

DATA : it_vbak TYPE STANDARD TABLE OF t_vbak,

wa_vbak TYPE t_vbak.

&--

FOR TABLE ZVBAP--

DATA : it_vbap TYPE STANDARD TABLE OF t_vbap,

wa_vbap TYPE t_vbap,

it_temp TYPE STANDARD TABLE OF t_vbap.

&--

FOR TABLE ZMARA--

DATA : it_mara TYPE STANDARD TABLE OF t_mara,

wa_mara TYPE t_mara.

&----

FOR ALL THREE TABLES----

DATA : it_final TYPE STANDARD TABLE OF t_final,

wa_final TYPE t_final.

&----

DATA : var TYPE zvbak-vbeln.

&--

SELECT OPTIONS FOR VBELN--

SELECT-OPTIONS: s_vbeln FOR var.

START-OF-SELECTION.

&--

SELECT INTO TABLE ZVBAK--

SELECT vbeln vkorg vtweg spart

FROM zvbak

INTO TABLE it_vbak

WHERE

vbeln IN s_vbeln

ORDER BY vbeln.

&--

SELECT INTO TABLE ZVBAP--

IF sy-subrc = 0 .

SELECT vbeln posnr matnr matkl

FROM zvbap

INTO TABLE it_vbap

FOR ALL ENTRIES IN it_vbak

WHERE vbeln = it_vbak-vbeln.

it_temp[] = it_vbap[].

SORT it_temp BY matnr.

DELETE ADJACENT DUPLICATES FROM it_temp COMPARING matnr.

&--

SELECT INTO TABLE ZMARA--

IF sy-subrc = 0.

SELECT matnr mbrsh meins

FROM zmara

INTO TABLE it_mara

FOR ALL ENTRIES IN it_temp

WHERE matnr = it_temp-matnr.

ENDIF.

&--

SELECT INTO FINAL TABLE--

LOOP AT it_vbap INTO wa_vbap.

READ TABLE it_vbak INTO wa_vbak WITH KEY vbeln = wa_vbap-vbeln.

wa_final-vbeln = wa_vbak-vbeln.

wa_final-vkorg = wa_vbak-vkorg.

wa_final-vtweg = wa_vbak-vtweg.

wa_final-spart = wa_vbak-spart. --->Populating Final Internal table

wa_final-posnr = wa_vbap-posnr.

wa_final-matnr = wa_vbap-matnr.

wa_final-matkl = wa_vbap-matkl.

READ TABLE it_mara INTO wa_mara WITH KEY matnr = wa_vbap-matnr.

wa_final-mbrsh = wa_mara-mbrsh.

wa_final-meins = wa_mara-meins.

APPEND wa_final TO it_final.

ENDLOOP.

END-OF-SELECTION.

&--

PRINT ALL THE RECORDS--

WRITE : /1 'VBELN',10 'VKORG',19 'VTWEG',29 'SPART',40 'POSNR',50 'MATNR',59 'MATKL',69 'MBRSH',79 'MEINS'.

LOOP AT it_final INTO wa_final.

AT NEW spart.

ULINE.

WRITE : /1 wa_final-vbeln, 10 wa_final-vkorg,20 wa_final-vtweg,30 wa_final-spart.

ENDAT.

WRITE : 40 wa_final-posnr,50 wa_final-matnr,60 wa_final-matkl,70 wa_final-mbrsh,80 wa_final-meins.

WRITE: /.

ENDLOOP.

ULINE.

Hope it helps you

Regards Mansi

Re: Regarding select query

Former Member — Thu, 18 Feb 2010 07:40:33 GMT

thanks for ur help,

issue got solved