Left outer join doubt

[Former Member]

Hi,

SELECT A_{VBELN B}GBSTK

INTO (VBUK-VBELN, VBUK-GBSTK)

FROM VAKPA AS A

LEFT OUTER JOIN

VBUK AS B

ON A_{VBELN = B}VBELN

WHERE A~KUNDE = ITAB_DUPS-KUNNR1

AND A~PARVW = 'AG'

AND A~VKORG = P_SALEOR

AND B~GBSTK IN S_GBSTK.

It is showing a syntax error in ECC 5 version where as its not showing any error in 3.1i.

The syntax error is

<b>No fields from the right-hand table of a LEFT OUTER JOIN may appear in the Where condition.</b>

Helpful answers will be rewarded.

Thanks

[RichHeilman]

You can not have a field from the RIGHT side of the join in your WHERE clause. This is not allow. To get around this, you can simply check it afterwards.

SELECT A~VBELN B~GBSTK
INTO (VBUK-VBELN, VBUK-GBSTK)
FROM VAKPA AS A
LEFT OUTER JOIN 
VBUK AS B
ON A~VBELN = B~VBELN
WHERE A~KUNDE = ITAB_DUPS-KUNNR1
AND A~PARVW = 'AG'
AND A~VKORG = P_SALEOR.
<b>*AND B~GBSTK IN S_GBSTK.</b> 

if vbuk-gbstk in s_gbstk.
* do something
endif.

REgards,

Rich Heilman

[Former Member]

Hi Rich,

if vbuk-gbstk in s_gbstk.

• do something

endif.

There is nothing to be done....in the if condition

<b>if vbuk-gbstk in s_gbstk</b>.

I have to select values using the below join and store them in VBUK-VBELN, VBUK-GBSTK.

If I comment the last line in the select then how can I

retrieve the data using the join into VBUK-VBELN, VBUK-GBSTK. I am missing out that condition right.

SELECT A_{VBELN B}GBSTK

INTO (VBUK-VBELN, VBUK-GBSTK)

FROM VAKPA AS A

LEFT OUTER JOIN

VBUK AS B

ON A_{VBELN = B}VBELN

WHERE A~KUNDE = ITAB_DUPS-KUNNR1

AND A~PARVW = 'AG'

AND A~VKORG = P_SALEOR.

*AND B~GBSTK IN S_GBSTK.

Thanks

Kalyani

[Former Member]

Hi Kalyani,

Try as follows:

SELECT A_{VBELN B}GBSTK

INTO (VBUK-VBELN, VBUK-GBSTK)

FROM VAKPA AS A

LEFT OUTER JOIN

VBUK AS B

ON A_{VBELN = B}VBELN

WHERE A~KUNDE = ITAB_DUPS-KUNNR1

AND A~PARVW = 'AG'

AND A~VKORG = P_SALEOR.

<b>*AND B~GBSTK IN S_GBSTK</b>

<b>IF VBUK-GBSTK IN S_GBSTK.

MODIFY VBUK INDEX SY-TABIX.

ENDIF.</b>

<b>ENDSELECT.</b>

Thanks,

Vinay

[RichHeilman]

From what I can tell, you are just filling the fields of the structure VBUK for VBELN and GBSTK. You can simply clear them out if the condition does not pass.

SELECT A~VBELN B~GBSTK
INTO (VBUK-VBELN, VBUK-GBSTK)
FROM VAKPA AS A
LEFT OUTER JOIN 
VBUK AS B
ON A~VBELN = B~VBELN
WHERE A~KUNDE = ITAB_DUPS-KUNNR1
AND A~PARVW = 'AG'
AND A~VKORG = P_SALEOR.
*AND B~GBSTK IN S_GBSTK
<b>IF VBUK-GBSTK IN S_GBSTK.
  clear vbuk.
  continue.
endif.</b>

 
ENDSELECT.

CONTINUE will jump to the next loop in the SELECT.

Regards,

Rich Heilman

[Former Member]

Hi Kalyani,

Please check this thread...

Hope it helps you!

cheers

Prashanth

P.S Please mark helpful answers