topic Re: Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Thu, 28 Sep 2006 18:09:42 GMT

Hi friends,

I’m developing a report and have almost done. But my inner join query is not fetching records correctly.i’ve a selection screen with following inputs.

1. docno select-options (like bkpf-belnr)

2.doctyp select-options (like bkpf-blart)

3.date select-options (like bkpf-cpudt)

4.user parameter (like bkpf-usnam)

5. refdocno select-options (like bkpf-xblnr)

I need following output in my report.

1.Document Number BKPF- BELNR

2.Reference Document Number BKPF- XBLNR

3.Entered By BKPF- CPUDT

4.Account Type BSEG- KOART

5.Cost Center BSEG- KOSTL

6.WBS Element BSEG- PROJK

7.Vendor Number BSEG- LIFNR

8.Debit / Credit Indicator BSEG- SHKZG

9.Amount BSEG- WRBTR

10.Vendor Name BSEC- NAME1

11.Name 2 BSEC- NAME2

12.Name 3 BSEC- NAME3

13.Vendor Address BSEC- STRAS

14.Vendor City BSEC- ORT01

15.Vendor State / Province BSEC- REGIO

16.Vendor Zip Code BSEC- PSTLZ

Please help me as it urgent. Our go-live is next week. I promise I will award points.

-Ashley

Re: Inner Join

Former Member — Thu, 28 Sep 2006 18:13:20 GMT

HI Ashley,

My adivce if u stuck in inner join statement and it is so urgent then go for normal select , for all entries and loop statements until u get the correct combination of joins..

but i am not sure that u can join those tables..

regards

Re: Inner Join

Former Member — Thu, 28 Sep 2006 18:37:08 GMT

You cannot join with BSEG and BSEG as they are cluster tables. Select from BKPF into a table. Loop at that table selecting from BSEG and BSEC independently to build your report table.

Re: Inner Join

Former Member — Thu, 28 Sep 2006 18:57:35 GMT

Try something like this:


TABLES: BKPF.

SELECT-OPTIONS: DOCNO    FOR BKPF-BELNR,
                DOCTYP   FOR BKPF-BLART,
                DATE     FOR BKPF-CPUDT,
                USER     FOR BKPF-USNAM,
                REFDOCNO FOR BKPF-XBLNR.

DATA: BEGIN OF T_BKPF OCCURS 0,
        BUKRS LIKE BKPF-BUKRS,
        BELNR LIKE BKPF-BELNR,
        GJAHR LIKE BKPF-GJAHR,
        XBLNR LIKE BKPF-XBLNR,
        CPUDT LIKE BKPF-CPUDT,
      END OF T_BKPF.

DATA: BEGIN OF T_BSEG OCCURS 0,
        BUKRS LIKE BSEG-BUKRS,
        BELNR LIKE BSEG-BELNR,
        GJAHR LIKE BSEG-GJAHR,
        KOART LIKE BSEG-KOART,
        KOSTL LIKE BSEG-KOSTL,
        PROJK LIKE BSEG-PROJK,
        LIFNR LIKE BSEG-LIFNR,
        SHKZG LIKE BSEG-SHKZG,
        WRBTR LIKE BSEG-WRBTR,
      END OF T_BSEG.

DATA: BEGIN OF T_BSEC OCCURS 0,
        BUKRS LIKE BSEC-BUKRS,
        BELNR LIKE BSEC-BELNR,
        GJAHR LIKE BSEC-GJAHR,
        NAME1 LIKE BSEC-NAME1,
        NAME2 LIKE BSEC-NAME2,
        NAME3 LIKE BSEC-NAME3,
        STRAS LIKE BSEC-STRAS,
        ORT01 LIKE BSEC-ORT01,
        REGIO LIKE BSEC-REGIO,
        PSTLZ LIKE BSEC-PSTLZ,
      END OF T_BSEC.

SELECT BUKRS BELNR GJAHR XBLNR CPUDT
   INTO TABLE T_BKPF
   FROM BKPF
  WHERE BELNR IN DOCNO
    AND BLART IN DOCTYP
    AND CPUDT IN DATE
    AND USNAM IN USER
    AND XBLNR IN REFDOCNO.

CHECK NOT T_BKPF[] IS INITIAL.

SELECT BUKRS BELNR GJAHR KOART KOSTL PROJK LIFNR SHKZG WRBTR
   INTO TABLE T_BSEG
   FROM BSEG
    FOR ALL ENTRIES IN T_BKPF
  WHERE BUKRS = T_BKPF-BUKRS
    AND BELNR = T_BKPF-BELNR
    AND GJAHR = T_BKPF-GJAHR.


SELECT BUKRS BELNR GJAHR NAME1 NAME2 NAME3 STRAS ORT01 REGIO PSTLZ
   INTO TABLE T_BSEC
   FROM BSEC
    FOR ALL ENTRIES IN T_BKPF
  WHERE BUKRS = T_BKPF-BUKRS
    AND BELNR = T_BKPF-BELNR
    AND GJAHR = T_BKPF-GJAHR.

SORT: T_BKPF, T_BSEG, T_BSEC.

LOOP AT T_BKPF.
  WRITE: / T_BKPF-BELNR,
           T_BKPF-XBLNR,
           T_BKPF-CPUDT.

  LOOP AT T_BSEG WHERE BUKRS = T_BKPF-BUKRS
                   AND BELNR = T_BKPF-BELNR
                   AND GJAHR = T_BKPF-GJAHR.
    WRITE: /10 T_BSEG-KOART,
               T_BSEG-KOSTL,
               T_BSEG-PROJK,
               T_BSEG-LIFNR,
               T_BSEG-SHKZG,
               T_BSEG-WRBTR.
  ENDLOOP.

  LOOP AT T_BSEC WHERE BUKRS = T_BKPF-BUKRS
                   AND BELNR = T_BKPF-BELNR
                   AND GJAHR = T_BKPF-GJAHR.
     WRITE: /10 T_BSEC-NAME1,
                T_BSEC-NAME2,
                T_BSEC-NAME3,
                T_BSEC-STRAS,
                T_BSEC-ORT01,
                T_BSEC-REGIO,
                T_BSEC-PSTLZ.

  ENDLOOP.
ENDLOOP.

Re: Inner Join

Former Member — Thu, 28 Sep 2006 20:29:32 GMT

Follow the below logic..as it is not possible to do inner join with BSEG.

SELECT fields from BKPF into table itab

where filter conditions.

if itab[] is not initial.

select fields for bseg into table ibseg

for all enteries in itab

from bseg where filter = itab-fields.

similarly do for bsec

endif.

loop at itab

read ibseg

read ibsec

endloop.

Re: Inner Join

Former Member — Mon, 02 Oct 2006 20:19:41 GMT

hi friends,

Thank you for that quick reply. It was especially helpful.Points are awarded.

thanks

Ashli