topic Re: Need help SELECT wiht INNER JOIN in Application Development and Automation Discussions

Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 16:06:48 GMT

Hi,

I need the the entire record of the MVKE with the same vkorg as in the table YMMARKET. Why has the bellow Join syntex error. Thank you

SELECT *

FROM MVKE

INNER JOIN YMMARKET_CODE ON mvke_{vkorg = ymmarket_code}vkorg

WHERE matnr = p_matnr.

ENDSELECT.

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 16:12:33 GMT

Hello,

Do this:


DATA:
  lt_mvke TYPE STANDARD TABLE OF MVKE.

SELECT <field1> <field2>
INTO TABLE lt_mvke
FROM MVKE AS M INNER JOIN YMMARKET_CODE AS Y ON m~vkorg = y~vkorg
WHERE matnr = p_matnr.

When using inner join you need to specify the fields.

I suggest you the following:


SELECT * FROM MVKE INTO TABLE lt_mvke
  FOR ALL ENTRIES IN YMMARKET_CODE
  WHERE matnr = p_matnr 
       AND vkorg = ymmarket_code-vkorg.

Regards,

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 16:18:48 GMT

whenever u join two table using joins u should use alias names

ex:

parameter:p_matnr type mara-matnr.

select a~matnr 
          a~mbrsh
          a~mbrsh
          b~maktx
into table itab
where mara as a inner join makt as b on a~matnr eq b~matnr
                                           and a~matnr eq p_matnr.

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 16:22:28 GMT

select vkorg into table i_vkorg from ymmarket.

select * from mvke for all entries in i_vkorg where vkorg = i_vkorg-vkorg

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 16:23:09 GMT

It returns an syntax error with "You can not use an internal table as a work area"

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 16:29:42 GMT

Hello,

Try this:


DATA:
  lt_mvke TYPE TABLE OF mvke,
  ls_mvke LIKE LINE OF lt_mvke,
  lt_ymmarket TYPE TABLE OF ymmarket_code-vkorg.

SELECT * FROM MVKE INTO TABLE lt_mvke WHERE matnr = p_matnr.
IF NOT lt_mvke IS INITIAL.
  SELECT vkorg FROM YMMARKET_CODE INTO TABLE lt_ymmarket
    FOR ALL ENTRIES IN lt_mvke WHERE vkorg = lt_mvke-vkorg.

  LOOP AT lt_mvke INTO ls_mvke.
    READ TABLE lt_ymmarket WITH KEY vkorg = ls_mvke-vkorg TRANSPORTING NO FIELDS.
    IF SY-SUBRC NE 0.
      DELETE LT_MVKE.
    ENDIF.
  ENDLOOP.
ENDIF.

Regards,

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 16:58:53 GMT

Do I really need the i_tab ?

And how should I define the itab when I need all fields of the entire record of the table mara for example?

Regards

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 17:18:38 GMT

Hello,

Using this method that I described you get a better performance than using INNER JOINS.

To define tables with all fields of a structure/transparent table defined in the DDIC just do the following:


  DATA lt_itab TYPE TABLE OF <structure/transparent table>.

Regards,

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 17:33:07 GMT

Thank you very much for your respond. However I couldn't understand your coding much. Instead would you please look to my without the Join

Read the Sales data for Material

SELECT *

FROM MVKE INTO TABLE i_mvke

WHERE matnr = p_matnr.

LOOP AT i_mvke INTO i_mvke.

SELECT SINGLE vkorg

FROM YMMARKET_CODE

INTO YMMARKET_CODE-vkorg

WHERE vkorg = i_mvke.

IF sy-subrc = 0.

exit.

ENDIF.

ENDLOOP.

I just want to take the record of the MVKE which has the same "vkorg" in the table YMMARKET_CODE

Thanks

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 17:47:31 GMT

Why did u dropped your join idea ?

anyways, for thsi code,

after looping you need to delete those entries, which dont have a hit from YMMARKET_CODE, right ?

SELECT *

FROM MVKE INTO TABLE i_mvke

WHERE matnr = p_matnr.

LOOP AT i_mvke . "INTO i_mvke.

SELECT SINGLE vkorg

FROM YMMARKET_CODE

INTO YMMARKET_CODE-vkorg

WHERE vkorg = i_mvke-vkorg.

IF sy-subrc ne 0.

delete i_mvke.

continue.

ENDIF.

ENDLOOP.

now at the end of this loop, i_mvke, will have all those entries, which have a corresponding hit in

YMMARKET_CODE, for the VKORG.

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 18:03:17 GMT

It didn't work with the Join and I had not much time left to spend on this, but you are right, with the i_mvke-vkorg. I just copied it before writing this to end. But from the performance point of view which solution is better. Join or select with loop? Sorry that I keep asking you

/Thanks

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 18:09:53 GMT

Hello,

revise your code like this:

SELECT *

FROM MVKE

INNER JOIN YMMARKET_CODE ON mvke_{vkorg = ymmarket_code}vkorg

WHERE mvke~matnr = p_matnr.

ENDSELECT.

I hope help you

David

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 18:20:54 GMT

The INTO is missing

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 18:26:53 GMT

you need create a internal table with the all fields the both tables, similar this code:

SELECT

ANEK~BUKRS

ANEK~ANLN1

ANEK~ANLN2

ANEK~GJAHR

ANEK~LNRAN

ANEK~BUDAT

ANEP~AFABE

ANEP~BWASL

ANEP~ANBTR

ANEP~NAFAB

ANEP~SAFAB

ANEP~ZINSB

INTO CORRESPONDING FIELDS OF TABLE IT_ANEK_P

FROM ( ANEK INNER JOIN ANEP

ON ANEP_{BUKRS = ANEK}BUKRS

AND ANEP_{ANLN1 = ANEK}ANLN1

AND ANEP_{ANLN2 = ANEK}ANLN2

AND ANEP_{GJAHR = ANEK}GJAHR

AND ANEP_{LNRAN = ANEK}LNRAN )

WHERE ANEK~BUKRS = <IT_SOC>-BUKRS

AND ANEK~ANLN1 IN S_ANLN1

AND ANEK~GJAHR = v_ANO

AND ANEK~BUDAT IN RDATE

AND ANEP~AFABE <> '15'

AND ( ( ANEP~BWASL IN RALTAS ) OR

( ANEP~BWASL IN RBAJAS ) OR

( ANEP~BWASL IN RTRASL ) ).

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 18:28:08 GMT

data : t_mvke type standard table of mvke.

SELECT *

FROM MVKE

INNER JOIN YMMARKET_CODE ON mvke_{vkorg = ymmarket_code}vkorg

into corresponding fields of table t_mvke

WHERE mvke~matnr = p_matnr.

Re: Need help SELECT wiht INNER JOIN

Former Member — Thu, 15 May 2008 19:03:11 GMT

Thanks it works now how it should.