topic Left outer join doubt in Application Development and Automation Discussions

Left outer join doubt

Former Member — Mon, 21 Aug 2006 18:30:43 GMT

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

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

Helpful answers will be rewarded.

Thanks

Re: Left outer join doubt

RichHeilman — Mon, 21 Aug 2006 18:36:34 GMT

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

Re: Left outer join doubt

Former Member — Mon, 21 Aug 2006 18:37:30 GMT

Hi Kalyani,

Please check this thread...

Hope it helps you!

cheers

Prashanth

P.S Please mark helpful answers

Re: Left outer join doubt

Former Member — Mon, 21 Aug 2006 18:43:36 GMT

Hi Rich,

if vbuk-gbstk in s_gbstk.

do something

endif.

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

if vbuk-gbstk in s_gbstk.

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

Re: Left outer join doubt

Former Member — Mon, 21 Aug 2006 18:50:52 GMT

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.

*AND B~GBSTK IN S_GBSTK

IF VBUK-GBSTK IN S_GBSTK.

MODIFY VBUK INDEX SY-TABIX.

ENDIF.

ENDSELECT.

Thanks,

Vinay

Re: Left outer join doubt

RichHeilman — Mon, 21 Aug 2006 19:02:15 GMT

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