Inner join Statement

[Former Member]

Hi All,

I am using this inner Join Statement

SELECT *

FROM vbap AS a INNER JOIN vbak AS b

ON a_{vbeln = b}vbeln

AND a_{mandt = b}mandt

WHERE a~vbeln IN lr_vbeln AND

a~posnr IN lr_posnr AND

a~aedat IN lr_aedat AND

( b~vbtyp EQ 'A' OR

b~vbtyp EQ 'B' OR

b~vbtyp EQ 'G' ).

But when i see the Output, One of the field ERDAT is geeting picked up from VBAK table instead of VBAP table.

Any pointers on how can we select the ERDAT from VBAP using this inner Join.

I dont want to use 2 select stetements.

Regards,

Mayank

[Former Member]

Hi Mayank,

try this way..

data : it_vbap type standard table of vbap with header line.  "add this

       SELECT *
       FROM vbap AS a INNER JOIN vbak AS b
                      ON a~vbeln = b~vbeln
                      AND a~mandt = b~mandt
      into corresponding field of it_vbap "add this
       WHERE a~vbeln IN lr_vbeln AND
             a~posnr IN lr_posnr AND
             a~aedat IN lr_aedat AND
            b~vbtyp in ( 'A' , 'B' , 'G' ).      "add this
 

Regards,

Prabhudas

[Former Member]

Hi Prabhu,

The Code which you have provided will not work.

Any other inputs on this issue.

[Former Member]

Hi Mayank,

What ouput are you seeing in the above statement?

You can directly use VBAP-ERDAT or VBAK-ERDAT, as the data will be stored in the header line.

[Former Member]

Please post the actual statement that you are using.

Rob

[mvoros]

Hi,

you can select fields using syntax like a~erdat. So you need to write down all fields which you need from your query instead of using *.

Cheers

[former_member229729]

Hi,

During inner joins using Alias ('A' or 'B' and so on), we have to instruct, what field from which Alias need to be selected from the Query.

Regards,

RamaniN

[Former Member]

In Inner join, it is always advisable to put the header table as First table and secondly, it should be advisable to fetch only the requried fields. I believe that you might not required all the fields of VBAP table.

Select A~Vbeln
          A~vbtyp
          B~posnr
          B~erdat
          into table I_VBAP
          from vbak as A innner Join VBAP as B
          on  a~vbeln = b~vbeln
          where   a~vbeln IN lr_vbeln AND
                      b~posnr IN lr_posnr AND
                      b~aedat IN lr_aedat AND
                     ( a~vbtyp EQ 'A' OR
                       a~vbtyp EQ 'B' OR
                       a~vbtyp EQ 'G' ).

[Former Member]

hi,

please use into corresponding fields

select * into corresponding fields of table <itab>

from ( VBAK AS A INNER JOIN VBAP AS B ON

A_{VBELN = B}VBELN )

WHERE a~vbeln IN lr_vbeln AND

b~posnr IN lr_posnr AND

b~aedat IN lr_aedat AND

( a~vbtyp EQ 'A' OR

a~vbtyp EQ 'B' OR

a~vbtyp EQ 'G' ).

[Former Member]

done