topic Re: Inner join Statement in Application Development and Automation Discussions

Inner join Statement

Former Member — Tue, 19 May 2009 17:43:50 GMT

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

Re: Inner join Statement

Former Member — Tue, 19 May 2009 17:51:33 GMT

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

Re: Inner join Statement

Former Member — Tue, 19 May 2009 18:07:43 GMT

Please post the actual statement that you are using.

Rob

Re: Inner join Statement

Former Member — Tue, 19 May 2009 23:02:39 GMT

Hi Prabhu,

The Code which you have provided will not work.

Any other inputs on this issue.

Re: Inner join Statement

Former Member — Tue, 19 May 2009 23:38:07 GMT

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.

Re: Inner join Statement

mvoros — Wed, 20 May 2009 02:38:12 GMT

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

Re: Inner join Statement

former_member229729 — Wed, 20 May 2009 03:18:22 GMT

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

Re: Inner join Statement

Former Member — Wed, 20 May 2009 04:08:16 GMT

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' ).

Re: Inner join Statement

Former Member — Wed, 20 May 2009 09:59:33 GMT

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' ).

Re: Inner join Statement

Former Member — Thu, 18 Jun 2009 17:39:39 GMT

done