Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

regarding joins

Former Member
0 Kudos

hi experts,

i am sending you the code.i have used the following join conditions.but it is not fetching any data.plz check it once.make the corrections if required or suggest me what else i can do...

PARAMETER CMP_CD LIKE BKPF-BUKRS.

SELECT-OPTIONS VENDOR FOR BSIK-LIFNR.

SELECT-OPTIONS P_DATE FOR BKPF-BUDAT.

DATA: BEGIN OF STRUCT,

BELNR LIKE VBRK-BELNR,

LIFNR LIKE BSIK-LIFNR,

BUDAT LIKE BKPF-BUDAT,

VBELN LIKE VBRK-VBELN,

ORT01 LIKE LFA1-ORT01,

ORT02 LIKE LFA1-ORT02,

PSTLZ LIKE LFA1-PSTLZ,

STRAS LIKE LFA1-STRAS,

END OF STRUCT.

DATA ITAB LIKE STRUCT OCCURS 0 WITH HEADER LINE.

AT SELECTION-SCREEN.

CASE SY-UCOMM.

WHEN ' '.

SELECT AVBELN BLIFNR ABELNR CBUDAT DORT01 DORT02 DPSTLZ DSTRAS

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM BKPF AS C INNER JOIN VBRK AS A ON ( CBUKRS = ABUKRS ) AND (

CBELNR = ABELNR ) INNER JOIN BSIK AS B ON ( ABUKRS = BBUKRS ) AND (

ABELNR = BBELNR ) INNER JOIN LFA1 AS D ON ( BLIFNR = DLIFNR )

WHERE CBUDAT IN P_DATE AND BLIFNR IN VENDOR AND C~BUKRS = CMP_CD.

regards,

raman

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi,

Refer to the following join statement:

SELECT AEQUNR BDATAB BILOAN CSWERK

INTO TABLE ITEQ

FROM ( EQUI AS A INNER JOIN EQUZ AS B

ON AEQUNR = BEQUNR ) INNER JOIN ILOA AS C

ON BILOAN = CILOAN

WHERE A~EQART = 'ESTPL'

AND B~IWERK = 'M011'

AND B~INGRP = 'SLM'

AND BDATAB GE SDATE-LOW AND BDATAB LE SDATE-HIGH

AND C~SWERK IN S_WERK.

Hope this helps.

Reward if helpful.

Regards,

Sipra

6 REPLIES 6

Former Member
0 Kudos

can you just paste your whole code.

Also remove AT SELECTION SCREEN if you are not doing anything there.

Regards,

Atish

0 Kudos

HI AATISH,

i am sending you the full code..plz help me out..my internal table is not fetching any data...there is some problem.

REPORT ZFI_REPORT_VENDOR .

TABLES: BSEG,LFA1,T001,BKPF,TVZBT,TVFKT,VBRK,ADRC,T005T,EKBE,BSIK.

PARAMETER CMP_CD LIKE BKPF-BUKRS.

SELECT-OPTIONS VENDOR FOR BSIK-LIFNR.

SELECT-OPTIONS P_DATE FOR BKPF-BUDAT.

DATA: BEGIN OF ITAB OCCURS 0,

BELNR LIKE VBRK-BELNR,

LIFNR LIKE BSIK-LIFNR,

BUDAT LIKE BKPF-BUDAT,

VBELN LIKE VBRK-VBELN,

ORT01 LIKE LFA1-ORT01,

ORT02 LIKE LFA1-ORT02,

PSTLZ LIKE LFA1-PSTLZ,

STRAS LIKE LFA1-STRAS,

END OF ITAB.

AT SELECTION-SCREEN.

CASE SY-UCOMM.

WHEN ' '.

SELECT AVBELN BLIFNR ABELNR CBUDAT DORT01 DORT02 DPSTLZ DSTRAS

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM BKPF AS C INNER JOIN VBRK AS A ON ( CBUKRS = ABUKRS ) AND (

CBELNR = ABELNR ) INNER JOIN BSIK AS B ON ( ABUKRS = BBUKRS ) AND (

ABELNR = BBELNR ) INNER JOIN LFA1 AS D ON ( BLIFNR = DLIFNR )

WHERE CBUDAT IN P_DATE AND BLIFNR IN VENDOR AND C~BUKRS = CMP_CD.

EXPORT ITAB TO MEMORY ID 'RAMAN'.

SUBMIT ZFI_REPORT_VENDOR1 VIA SELECTION-SCREEN AND RETURN.

.

ENDCASE.

REPORT ZFI_REPORT_VENDOR1 .

TABLES: BSEG,LFA1,T001,BKPF,TVZBT,TVFKT,VBRK,ADRC,T005T,EKBE,BSIK.

DATA: BEGIN OF ITAB OCCURS 0,

BELNR LIKE VBRK-BELNR,

LIFNR LIKE BSIK-LIFNR,

BUDAT LIKE BKPF-BUDAT,

VBELN LIKE VBRK-VBELN,

ORT01 LIKE LFA1-ORT01,

ORT02 LIKE LFA1-ORT02,

PSTLZ LIKE LFA1-PSTLZ,

STRAS LIKE LFA1-STRAS,

END OF ITAB.

WRITE:/ 'NAME'.

LOOP AT ITAB.

WRITE:/ ITAB-VBELN, ITAB-STRAS, ITAB-ORT01,ITAB-BUDAT,ITAB-LIFNR,

ITAB-BELNR,ITAB-ORT02.

ENDLOOP.

INITIALIZATION.

IMPORT ITAB FROM MEMORY ID 'RAMAN'.

0 Kudos

hi,

First change this

REPORT ZFI_REPORT_VENDOR .

TABLES: BSEG,LFA1,T001,BKPF,TVZBT,TVFKT,VBRK,ADRC,T005T,EKBE,BSIK.

PARAMETER CMP_CD LIKE BKPF-BUKRS.

SELECT-OPTIONS VENDOR FOR BSIK-LIFNR.

SELECT-OPTIONS P_DATE FOR BKPF-BUDAT.

DATA: BEGIN OF ITAB OCCURS 0,

BELNR LIKE VBRK-BELNR,

LIFNR LIKE BSIK-LIFNR,

BUDAT LIKE BKPF-BUDAT,

VBELN LIKE VBRK-VBELN,

ORT01 LIKE LFA1-ORT01,

ORT02 LIKE LFA1-ORT02,

PSTLZ LIKE LFA1-PSTLZ,

STRAS LIKE LFA1-STRAS,

END OF ITAB.

SELECT AVBELN BLIFNR ABELNR CBUDAT DORT01 DORT02 DPSTLZ DSTRAS

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM BKPF AS C INNER JOIN VBRK AS A ON ( CBUKRS = ABUKRS ) AND (

CBELNR = ABELNR ) INNER JOIN BSIK AS B ON ( ABUKRS = BBUKRS ) AND (

ABELNR = BBELNR ) INNER JOIN LFA1 AS D ON ( BLIFNR = DLIFNR )

WHERE CBUDAT IN P_DATE AND BLIFNR IN VENDOR AND C~BUKRS = CMP_CD.

EXPORT ITAB TO MEMORY ID 'RAMAN'.

SUBMIT ZFI_REPORT_VENDOR1 VIA SELECTION-SCREEN AND RETURN.

Now to see if the JOIN is correct or not, go to SQVI and create the same kind of JOIN and see if you can find entry there. In this way you can validate your JOIN also.

Regards,

Atish

Former Member
0 Kudos

Hi

What is your actual requirement?

You can't join tables simple like that?

there should be some relation between them?

No Need to use BKPF table here?

If you wants to use VBRK table use KNA1 and BSID or BSAD tables

(to fetch account recivables data)

join VBRK-VBELN with BSID-VBELN and KNA1-KUNNR with VBRK-KUNAG and KNA1-KUNNR with BSID-KUNNR

If you wants to use LFA1 table then use BSIK or BSAK with table EKKO/EKPO

(to fetch account payables data)

LFA1-LIFNR = BSIK-LIFNR and EKKO-LIFNR = LFA1-LIFNR

<b>Reward points for useful Answers</b>

Regards

Anji

0 Kudos

HI ANJI,

can i use join conditions like this...

SELECT ABELNR ABUDAT AVBELN BLIFNR CORT01 CORT02 CPSTLZ CSTRAS

INTO CORRESPONDING FIELDS OF TABLE ITAB FROM BSID AS A INNER JOIN BSIK

AS B ON ( ABUKRS = BBUKRS ) AND ( ABELNR = BBELNR ) INNER JOIN LFA1

AS C ON ( BLIFNR = CLIFNR ) WHERE BLIFNR IN VENDOR AND ABUDAT IN

P_DATE AND A~BUKRS = CMP_CD.

regards,

raman.

Former Member
0 Kudos

Hi,

Refer to the following join statement:

SELECT AEQUNR BDATAB BILOAN CSWERK

INTO TABLE ITEQ

FROM ( EQUI AS A INNER JOIN EQUZ AS B

ON AEQUNR = BEQUNR ) INNER JOIN ILOA AS C

ON BILOAN = CILOAN

WHERE A~EQART = 'ESTPL'

AND B~IWERK = 'M011'

AND B~INGRP = 'SLM'

AND BDATAB GE SDATE-LOW AND BDATAB LE SDATE-HIGH

AND C~SWERK IN S_WERK.

Hope this helps.

Reward if helpful.

Regards,

Sipra