08-02-2007 7:01 AM
hi experts,
i got stuck in a big problem.i need to fetch the data from the 4 tables ie BSEG,BKPF,LFA1,VBRK.now because BSEG is a cluster table i could not use it in joins.yesterday someone recommended me to use FOR ALL ENTRIES.i have tried with this also but still it is not working.plz helm me out.i am sending you the code.plz make the changes where ever needed..
REPORT ZFI_R_VENDOR .
TABLES:BSEG,LFA1,BKPF,VBRK.
DATA: BEGIN OF ITAB OCCURS 0,
BUKRS LIKE BKPF-BUKRS,
BELNR LIKE BSEG-BELNR,
DOC LIKE BSEG-BELNR,
LIFNR LIKE BSEG-LIFNR,
BUDAT LIKE BKPF-BUDAT,
VBELN LIKE VBRK-VBELN,
ORT01 LIKE LFA1-ORT01,
PSTLZ LIKE LFA1-PSTLZ,
STRAS LIKE LFA1-STRAS,
NAME1 LIKE LFA1-NAME1,
KOART LIKE BSEG-KOART,
SHKZG LIKE BSEG-SHKZG,
DMBTR LIKE BSEG-DMBTR,
DEBIT LIKE BSEG-DMBTR,
CREDIT LIKE BSEG-DMBTR,
AMOUNT LIKE BSEG-DMBTR,
END OF ITAB.
DATA PTAB LIKE ITAB OCCURS 0 WITH HEADER LINE.
DATA PTAB1 LIKE ITAB OCCURS 0 WITH HEADER LINE.
DATA I_OUTPUT LIKE ITAB OCCURS 0 WITH HEADER LINE.
DATA ITAB1 LIKE ITAB OCCURS 0 WITH HEADER LINE.
DATA:WBUDAT LIKE BKPF-BUDAT,
WABUKRS LIKE BKPF-BUKRS,
WVBELN LIKE VBRK-VBELN,
WBUKRS LIKE VBRK-BUKRS,
WLIFNR LIKE LFA1-LIFNR.
DATA:WCREDIT LIKE BSEG-DMBTR VALUE '0',
WDEBIT LIKE BSEG-DMBTR VALUE '0'.
SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.
PARAMETER CMP_CD LIKE BKPF-BUKRS.
SELECT-OPTIONS:VENDOR FOR BSEG-LIFNR,
P_DATE FOR BKPF-BUDAT.
SELECTION-SCREEN END OF BLOCK B1.
SELECT BUDAT BUKRS FROM BKPF INTO CORRESPONDING FIELDS OF TABLE ITAB WHERE BUDAT IN P_DATE AND BUKRS = CMP_CD.
SELECT BUKRS BELNR SHKZG DMBTR FROM BSEG INTO CORRESPONDING FIELDS OF TABLE PTAB FOR ALL ENTRIES IN ITAB
WHERE LIFNR IN VENDOR
AND BUKRS = ITAB-BUKRS.
SELECT VBELN FROM VBRK INTO CORRESPONDING FIELDS OF TABLE PTAB1 FOR ALL ENTRIES IN PTAB WHERE BUKRS = PTAB-BUKRS.
SELECT NAME1 ORT01 PSTLZ STRAS FROM LFA1 INTO CORRESPONDING FIELDS OF TABLE ITAB1 FOR ALL ENTRIES IN PTAB WHERE LIFNR = PTAB-LIFNR.
MOVE-CORRESPONDING ITAB TO I_OUTPUT.
MOVE-CORRESPONDING ITAB1 TO I_OUTPUT.
MOVE-CORRESPONDING PTAB TO I_OUTPUT.
MOVE-CORRESPONDING PTAB1 TO I_OUTPUT.
LOOP AT I_OUTPUT.
WRITE:/ I_OUTPUT-NAME1,I_OUTPUT-ORT01,I_OUTPUT-VBELN,I_OUTPUT-BELNR.
ENDLOOP.
regards,
raman.
08-02-2007 7:03 AM
all entiers rul
spcified all primary key in the table.
select primary key orderly.
08-02-2007 7:06 AM
plz tell me how could i specify those primary keys n also what would b the order.
08-02-2007 7:24 AM
Prakash,
Just some additional info
BSEG is a cluster table and fetching data from such an table will put the system to sleep when run in production and chances are more that you may get an error saying "execution time exceeded" resulting in no output.
So,always make it a point to have only primary keys fields or index fields in the where clause of a select statement.I say it is a must.
for ex
you have a select option field lifnr which is not a primary key field in the table from where you are fecthing the data then don't use this lifnr in the where clause of the select statement.Instead of that first select the data irrespective of the selection-screen fields into an internal table and later on filter it w.r.t to the selection screen fields.
loop at itab.
if lifnr not in s_lifnr.
delete itab.
endloop.
Hope this can be of some help to you as you are dealing with a cluster table.
K.Kiran.
08-02-2007 7:35 AM
Hi Prakash,
Let me share my points with you and for you.
DATA: BEGIN OF I_BKPF OCCURS 0,
BUKRS LIKE BKPF-BUKRS,
BUDATA LIKE BKPF-BUDAT,
END OF I_BKPF.
DATA: BEGIN OF I_BSEG OCCURS 0,
BUKRS LIKE BKPF-BUKRS,
BELNR LIKE BSEG-BELNR,
SHKZG LIKE BSEG-SHKZG,
DMBTR LIKE BSEG-DMBTR,
LIFNR LIKE BSEG-LIFNR,
END OF I_BSEG.
DATA: BEGIN OF I_VBRK OCCURS 0,
VBELN LIKE VBRK-VBELN,
END OF I_VBRK.
DATA: BEGIN OF I_VBRK OCCURS 0,
NAME1 LIKE LFA1-NAME1,
ORT01 LIKE LFA1-ORT01,
PSTLZ LIKE LFA1-PSTLZ,
STRAS LIKE LFA1-STRAS,
END OF I_LFA1.
*HERE DECLARE FINAL INTERNAL TABLE WITH REQUIRED FIELDS.
SELECT BUKRS
BUDAT
FROM BKPF INTO TABLE I_BKPF
WHERE BUKRS EQ CMP_CD
AND BUDAT IN P_DATE.
IF NOT I_BKPF[] IS INITIAL.
SELECT BUKRS
BELNR
SHKZG
DMBTR
LIFNR
FROM BSEG INTO TABLE I_BSEG
FOR ALL ENTRIES IN I_BKPF
WHERE BUKRS = I_BKPF-BUKRS
AND LIFNR IN VENDOR.
ENDIF.
IF NOT I_BSEG[] IS INITIAL.
SELECT VBELN
FROM VBRK
INTO TABLE ITAB2
FOR ALL ENTRIES IN I_VBRK
WHERE BUKRS = I_BSEG-BUKRS.
SELECT NAME1
ORT01
PSTLZ
STRAS
FROM LFA1
INTO TABLE I_LFA1
FOR ALL ENTRIES IN I_BSEG
WHERE LIFNR = I_BSEG-LIFNR.
ENDIF.
Now take final internal table and populate all thsese fields data into that final internal table manually by move statement. Here your selecting vendor from lfa1 table based on bseg data with out selecting lifnr from BSEG. While selecting we should maintain fields order as same as with data base table fields order.
Hope this helps you. Reply for queries, shall post the updates.
Regards.
Kumar