topic Re: regarding joins in Application Development and Automation Discussions

regarding joins

Former Member — Wed, 11 Jul 2007 06:44:15 GMT

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 A_{VBELN B}LIFNR A_{BELNR C}BUDAT D_{ORT01 D}ORT02 D_{PSTLZ D}STRAS

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM BKPF AS C INNER JOIN VBRK AS A ON ( C_{BUKRS = A}BUKRS ) AND (

C_{BELNR = A}BELNR ) INNER JOIN BSIK AS B ON ( A_{BUKRS = B}BUKRS ) AND (

A_{BELNR = B}BELNR ) INNER JOIN LFA1 AS D ON ( B_{LIFNR = D}LIFNR )

WHERE C_{BUDAT IN P_DATE AND B}LIFNR IN VENDOR AND C~BUKRS = CMP_CD.

regards,

raman

Re: regarding joins

Former Member — Wed, 11 Jul 2007 06:48:01 GMT

can you just paste your whole code.

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

Regards,

Atish

Re: regarding joins

Former Member — Wed, 11 Jul 2007 06:50:35 GMT

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

Re: regarding joins

Former Member — Wed, 11 Jul 2007 07:01:56 GMT

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 A_{VBELN B}LIFNR A_{BELNR C}BUDAT D_{ORT01 D}ORT02 D_{PSTLZ D}STRAS

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM BKPF AS C INNER JOIN VBRK AS A ON ( C_{BUKRS = A}BUKRS ) AND (

C_{BELNR = A}BELNR ) INNER JOIN BSIK AS B ON ( A_{BUKRS = B}BUKRS ) AND (

A_{BELNR = B}BELNR ) INNER JOIN LFA1 AS D ON ( B_{LIFNR = D}LIFNR )

WHERE C_{BUDAT IN P_DATE AND B}LIFNR 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'.

Re: regarding joins

Former Member — Wed, 11 Jul 2007 07:04:24 GMT

Hi,

Refer to the following join statement:

SELECT A_{EQUNR B}DATAB B_{ILOAN C}SWERK

INTO TABLE ITEQ

FROM ( EQUI AS A INNER JOIN EQUZ AS B

ON A_{EQUNR = B}EQUNR ) INNER JOIN ILOA AS C

ON B_{ILOAN = C}ILOAN

WHERE A~EQART = 'ESTPL'

AND B~IWERK = 'M011'

AND B~INGRP = 'SLM'

AND B_{DATAB GE SDATE-LOW AND B}DATAB LE SDATE-HIGH

AND C~SWERK IN S_WERK.

Hope this helps.

Reward if helpful.

Regards,

Sipra

Re: regarding joins

Former Member — Wed, 11 Jul 2007 07:09:57 GMT

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 A_{VBELN B}LIFNR A_{BELNR C}BUDAT D_{ORT01 D}ORT02 D_{PSTLZ D}STRAS

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM BKPF AS C INNER JOIN VBRK AS A ON ( C_{BUKRS = A}BUKRS ) AND (

C_{BELNR = A}BELNR ) INNER JOIN BSIK AS B ON ( A_{BUKRS = B}BUKRS ) AND (

A_{BELNR = B}BELNR ) INNER JOIN LFA1 AS D ON ( B_{LIFNR = D}LIFNR )

WHERE C_{BUDAT IN P_DATE AND B}LIFNR 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

Re: regarding joins

Former Member — Wed, 11 Jul 2007 07:41:30 GMT

HI ANJI,

can i use join conditions like this...

SELECT A_{BELNR A}BUDAT A_{VBELN B}LIFNR C_{ORT01 C}ORT02 C_{PSTLZ C}STRAS

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

AS B ON ( A_{BUKRS = B}BUKRS ) AND ( A_{BELNR = B}BELNR ) INNER JOIN LFA1

AS C ON ( B_{LIFNR = C}LIFNR ) WHERE B_{LIFNR IN VENDOR AND A}BUDAT IN

P_DATE AND A~BUKRS = CMP_CD.

regards,

raman.