topic Re: Good statement to remove this join statement in Application Development and Automation Discussions

Good statement to remove this join statement

Former Member — Thu, 10 Jan 2008 10:00:34 GMT

select PA0002_{NACHN PA0002}PERNR PA0002_{VORNA KNVP}KUNNR KNVP_{PARVW KNVP}PERNR KNVP_{SPART KNVP}VKORG KNVP_{VTWEG VBAK}AUART

VBAK_{ERDAT VBAK}ERNAM VBAK_{ERZET VBAK}FAKSK VBAK_{KUNNR VBAK}LIFSK VBAK_{SPART VBAK}VBELN VBAK_{VKORG VBAK}VTWEG VBUK~CMGST

VBUK_{VBELN TVLS}LIFSP

into (PA0002-NACHN , PA0002-PERNR , PA0002-VORNA , KNVP-KUNNR , KNVP-PARVW , KNVP-PERNR , KNVP-SPART , KNVP-VKORG , KNVP-VTWEG

, VBAK-AUART , VBAK-ERDAT , VBAK-ERNAM , VBAK-ERZET , VBAK-FAKSK , VBAK-KUNNR , VBAK-LIFSK , VBAK-SPART , VBAK-VBELN

, VBAK-VKORG , VBAK-VTWEG , VBUK-CMGST , VBUK-VBELN , TVLS-LIFSP )

from ( PA0002

inner join KNVP

on KNVP_{PERNR = PA0002}PERNR

inner join VBAK

on VBAK_{KUNNR = KNVP}KUNNR

and VBAK_{SPART = KNVP}SPART

and VBAK_{VKORG = KNVP}VKORG

and VBAK_{VTWEG = KNVP}VTWEG

inner join VBUK

on VBUK_{VBELN = VBAK}VBELN

inner join TVLS

on TVLS_{LIFSP = VBAK}LIFSK )

where PA0002~NACHN in SP$00003

and PA0002~PERNR in SP$00001

and KNVP~KUNNR in SP$00004

and KNVP~PARVW in SP$00014

and VBAK~AUART in SP$00005

and VBAK~ERDAT in SP$00011

and VBAK~ERNAM in SP$00013

and VBAK~ERZET in SP$00012

and VBAK~KUNNR in SP$00006

and VBAK~SPART in SP$00009

and VBAK~VBELN in SP$00010

and VBAK~VKORG in SP$00007

and VBAK~VTWEG in SP$00008.

Re: Good statement to remove this join statement

Kanagaraja_L — Thu, 10 Jan 2008 12:08:15 GMT

SELECT PA0002~NACHN

PA0002~PERNR

PA0002~VORNA

FROM PA0002

INTO (PA0002-NACHN, PA0002-PERNR, PA0002-VORNA)

WHERE PA0002~PERNR IN SP$00001.

IF SY-SUBRC EQ 0.

SELECT KNVP~KUNNR

KNVP~PARVW

KNVP~PERNR

KNVP~SPART

KNVP~VKORG

KNVP~VTWEG

FROM KNVP

INTO (KNVP-KUNNR, KNVP-PARVW, KNVP-PERNR, KNVP-SPART, KNVP-VKORG ,

KNVP-VTWEG)

FOR ALL ENTRIES IN PA0002

WHERE PERNR = PA0002

AND KNVP~KUNNR IN SP$00004

AND KNVP~PARVW IN SP$00014.

IF SY-SUBRC EQ 0.

SELECT VBAK~AUART

VBAK~ERDAT

VBAK~ERNAM

VBAK~ERZET

VBAK~FAKSK

VBAK~KUNNR

VBAK~LIFSK

VBAK~SPART

VBAK~VBELN

VBAK~VKORG

VBAK~VTWEG

FROM VBAK

INTO (VBAK-AUART, VBAK-ERDAT, VBAK-ERNAM, VBAK-ERZET,

VBAK-FAKSK, VBAK-KUNNR, VBAK-LIFSK, VBAK-SPART,

VBAK-VBELN, VBAK-VKORG, VBAK-VTWEG)

FOR ALL ENTRIES IN KNVP

WHERE KUNNR EQ KNVP-KUNNR

AND VBAK~AUART IN SP$00005

AND VBAK~ERDAT IN SP$00011

AND VBAK~ERNAM IN SP$00013

AND VBAK~ERZET IN SP$00012

AND VBAK~KUNNR IN SP$00006

AND VBAK~SPART IN SP$00009

AND VBAK~VBELN IN SP$00010

AND VBAK~VKORG IN SP$00007

AND VBAK~VTWEG IN SP$00008.

IF SY-SUBRC EQ 0.

SELECT VBUK~CMGST

VBUK~VBELN

INTO (VBUK-CMGST, VBUK-VBELN)

FROM VBUK

FOR ALL ENTRIES IN VBAK

WHERE VBELN EQ VBAK-VBELN.

IF SY-SUBRC EQ 0.

SELECT TVLS-LIFSP

INTO (TVLS-LIFSP)

FOR ALL ENTRIES IN VBAK

WHERE LIFSP EQ VBAK-LIFSP.

ENDIF.

Kanagaraja L

Re: Good statement to remove this join statement

Former Member — Thu, 10 Jan 2008 12:12:03 GMT

Hello,

Maybe you can define a custom View in which you stablish all the inner conditions and so.

Then you can use SELECT query to View similar to a Table, but without the joins.

I hope it will help you.