topic Re: Join not working in Application Development and Automation Discussions

Join not working

Former Member — Thu, 02 Apr 2009 16:16:20 GMT

Hello,

I have this code but it doesnt do not. This is a simple one with 2 tables. Given the case I need to join 5 table is the join the correct method because I heard that it is nort recomended for so many tables.

tables: mbew, marc, ZJOIN.

data :

i_zjoin type standard table of zjoin,

w_zjoin type zjoin.

select single a_{matnr a}bwkey a_{bwtar b}werks

into (zjoin-matnr, zjoin-bwkey, zjoin-bwtar, zjoin-werks)

from marc as b inner join mbew as a

on a_{matnr = b}matnr

where b~matnr = 'R2R-EQUIPMENT'.

Re: Join not working

Former Member — Thu, 02 Apr 2009 16:23:03 GMT

HI,

You can do join till 3 tables ...if you want to get the data from the 5 tables then you use the JOIn on the basic tables and then Using FOR ALL entries get the data from other table.

Check the below code

select single a~matnr a~bwkey a~bwtar b~werks
into corresponding field of table i_zjoin       
from marc as b inner join mbew as a
on a~matnr = b~matnr
where b~matnr = 'R2R-EQUIPMENT'.

IF NOT i_join[] is iniitial.
 SELECT * FROm marc into table i_marc 
   for all entries in i_zjoin 
    where matnr = i_zjoin-matnr and
             werks = i_zjoin-werks.
ENDIF.

Re: Join not working

Former Member — Thu, 02 Apr 2009 16:29:15 GMT

What do you mean that it is not working? Syntax error, dump, incorrect results?

Rob

Re: Join not working

Former Member — Thu, 02 Apr 2009 16:31:58 GMT

Hi ,

Now it gives this erroe

Field "corresponding is un known. It is nethier in one of the specified tables nor defined by a "data :" statement

Re: Join not working

Former Member — Thu, 02 Apr 2009 16:33:10 GMT

Zjoion dosent have anything after runnig the code.

Re: Join not working

Former Member — Thu, 02 Apr 2009 16:35:49 GMT

HI,

Check now ..it works

SELECT SINGLE a~matnr a~bwkey a~bwtar b~werks
FROM marc AS b INNER JOIN mbew AS a
INTO CORRESPONDING FIELDS OF TABLE i_zjoin
ON a~matnr = b~matnr
WHERE b~matnr = 'R2R-EQUIPMENT'.

IF NOT i_join[] IS INIITIAL.
  SELECT * FROM marc INTO  TABLE i_marc
    FOR ALL ENTRIES IN i_zjoin
     WHERE matnr = i_zjoin-matnr AND
              werks = i_zjoin-werks.
ENDIF.

Re: Join not working

Former Member — Thu, 02 Apr 2009 16:52:49 GMT

Still I get this error

Incorrect structure of FROM clause

Re: Join not working

Former Member — Thu, 02 Apr 2009 17:14:08 GMT

Hi David,

see the examples and use the appropriate one...

<< Cut and paste without attribution from http://www.saptechies.com/join-in-open-sql/ removed >>

Regards,

Prabhudas

Edited by: Rob Burbank on Apr 2, 2009 1:37 PM

Re: Join not working

Former Member — Thu, 02 Apr 2009 17:41:57 GMT

David - I don't see anything wrong with your original code. The fact that it doesn't return any value may just mean that there is no data to select. Have you run the material through the conversion exit before SELECTing?

Rob