Skip to Content

left join between two tables

to get only the data from the left table I have to use a right left join?

this is the code:

select *

from marc

left outer join mseg on marc ~ matnr = mseg ~ matnr and marc ~ werks = mseg ~ werks

into CORRESPONDING FIELDS OF TABLE it_marc

WHERE marc ~ WERKS in s_plant and mseg ~ CPUTM_MKPF> = sy-uzeit. .

gives me this error:

If new Open SQL syntax is used, all host variables must be escaped using @. The variable S_PLANT is not escaped.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Jul 24, 2020 at 03:43 PM

    What you're looking for doesn't imply a JOIN, it implies EXISTS:

    DATA dummy_plant TYPE marc-werks.
    SELECT-OPTIONS s_plant FOR dummy_plant.
    DATA it_marc TYPE TABLE OF marc.
    SELECT * FROM marc
      WHERE marc~werks IN @s_plant
        AND EXISTS (
          SELECT * FROM mseg
          WHERE marc~matnr = mseg~matnr
            AND marc~werks = mseg~werks
            AND mseg~cputm_mkpf >= @sy-uzeit )
     INTO TABLE @it_marc. 

    By the way, it seems obvious to me that you should test both CPUTM_MKPF and CPUDT_MKPF:

    AND ( mseg~CPUDT_MKPF > @sy-datum
       OR ( mseg~CPUDT_MKPF = @sy-datum
        AND mseg~cputm_mkpf >= @sy-uzeit ) )

    NB: I removed CORRESPONDING FIELDS because the target data object has the same structure as the database table.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jul 24, 2020 at 01:50 PM

    Hello nick ruju,

    You just have to use inner join which is left inner join by default. Error is due to missing @ symbol on all variables used which is the principle while using open SQL statements. Below code for your reference,

    select *
    from marc
    Inner join mseg 
    on marc ~ matnr = mseg ~ matnr and marc ~ werks = mseg ~ werks
    into CORRESPONDING FIELDS OF TABLE @it_marc
    WHERE marc ~ WERKS in @s_plant and mseg ~ CPUTM_MKPF> = @sy-uzeit. .
    

    Regards!

    Add a comment
    10|10000 characters needed characters exceeded

    • Sandra Rossi

      the it_marc table is the same as the marc table. I'm only interested in the values of the marc table, because for example using the filters in se16 in the marc there is only one corresponding row (and I want my internal table to have only that row) while in the mseg there are 36 rows. in the select it brings me all 36 rows when I just want one of the marc table

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.