topic regarding select statement in Application Development and Automation Discussions

regarding select statement

Former Member — Thu, 02 Aug 2007 06:01:05 GMT

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.

Re: regarding select statement

Former Member — Thu, 02 Aug 2007 06:03:24 GMT

all entiers rul

spcified all primary key in the table.

select primary key orderly.

Re: regarding select statement

Former Member — Thu, 02 Aug 2007 06:06:58 GMT

plz tell me how could i specify those primary keys n also what would b the order.

Re: regarding select statement

kiran_k8 — Thu, 02 Aug 2007 06:24:30 GMT

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.

Re: regarding select statement

Former Member — Thu, 02 Aug 2007 06:35:45 GMT

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