topic Re: Problems with joining tables in Application Development and Automation Discussions

Problems with joining tables

Former Member — Mon, 13 Feb 2006 20:12:31 GMT

Hello,

I am totally new at programming abap and would like som help with a join-statement.

I am wondering why this code generates four rows instead of 2 as a result? Is there some kind of nested loops going on? Can I solve this with inner or outer joins?

The code:

Report test.

TYPES: BEGIN OF itab,

ltxa1 LIKE afvc-ltxa1,

vornr LIKE afvc-vornr,

arbei LIKE afvv-arbei,

END OF itab.

DATA: it_temp TYPE table of itab,

wa_temp TYPE itab.

SELECT a_{ltxa1 a}vornr c~arbei

INTO (wa_temp-ltxa1, wa_temp-vornr, wa_temp-arbei)

FROM afvc AS a

JOIN caufv AS b

ON a_{aufpl = b}aufpl

OUTER JOIN afvv AS c

ON b_{aufpl = c}aufpl

WHERE b~aufnr = '000005000240'.

WRITE:/ wa_temp-ltxa1, wa_temp-vornr, wa_temp-arbei.

APPEND wa_temp TO it_temp.

ENDSELECT.

Regards

Tomas

Re: Problems with joining tables

Former Member — Mon, 13 Feb 2006 20:20:14 GMT

Hi Tomas,

Change it this way..

SELECT a_{ltxa1 a}vornr c~arbei

INTO (wa_temp-ltxa1, wa_temp-vornr, wa_temp-arbei)

FROM afvc AS a

JOIN caufv AS b

ON a_{aufpl = b}aufpl

<b>JOIN</b> afvv AS c

ON b_{aufpl = c}aufpl

WHERE b~aufnr = '000005000240'.

WRITE:/ wa_temp-ltxa1, wa_temp-vornr, wa_temp-arbei.

APPEND wa_temp TO it_temp.

ENDSELECT.

Re: Problems with joining tables

RichHeilman — Mon, 13 Feb 2006 20:23:03 GMT

I would suggest that you change your code to something like this. This works a lot better in my system.

This most important part here is that you are missing a key when join the AFVC and AFVV tables. You need to add APLZL.



report zrich_0003.


types: begin of itab,
ltxa1 like afvc-ltxa1,
vornr like afvc-vornr,
arbei like afvv-arbei,
end of itab.

data: it_temp type table of itab,
      wa_temp type itab.

select<b> b~ltxa1 b~vornr c~arbei</b>into (wa_temp-ltxa1, wa_temp-vornr, wa_temp-arbei)
<b>   from caufv as a
       inner join afvc as b
        on a~aufpl = b~aufpl</b>
          inner join afvv as c
                 on b~aufpl = c~aufpl
<b>                and b~APLZL = C~APLZL</b>     " <--- This right here  "
                       <b>where a~aufnr = '001004193793'.</b>
  write:/ wa_temp-ltxa1, wa_temp-vornr, wa_temp-arbei.
  append wa_temp to it_temp.
endselect.

Also, I have swapped the join for CAUFV and AFVC.

Regards,

Rich Heilman

Re: Problems with joining tables

Former Member — Mon, 13 Feb 2006 20:23:18 GMT

Nope, changing outer join to join didnt help.

I still get four rows as a result instead of 2

Re: Problems with joining tables

RichHeilman — Mon, 13 Feb 2006 20:25:49 GMT

Please see my previous post.

Regards,

Rich Heilman

Re: Problems with joining tables

Former Member — Mon, 13 Feb 2006 20:30:21 GMT

Thanks a lot Rich! This did the job.

Now it works great. This forum is truly amazing, and you guys are great. Keep it up.

Can you explain your code to me? This aplzl field, is it an internal counter or what does it do?

Regards

Tomas

Re: Problems with joining tables

Former Member — Mon, 13 Feb 2006 20:31:35 GMT

Hi Tomas,

Change your internal table & populate the values like this to see whether your data is duplicating or not.

TYPES: BEGIN OF itab,

aufnr like caufv-aufnr,

aufpl like afvc-aufpl,

ltxa1 LIKE afvc-ltxa1,

vornr LIKE afvc-vornr,

arbei LIKE afvv-arbei,

END OF itab.

SELECT a_{aufnr b}aufpl a_{ltxa1 a}vornr c~arbei

INTO (wa_temp-aufnr, wa_temp-aufpl, wa_temp-ltxa1, wa_temp-vornr, wa_temp-arbei)

FROM afvc AS a

JOIN caufv AS b

ON a_{aufpl = b}aufpl

OUTER JOIN afvv AS c

ON b_{aufpl = c}aufpl

<b>b_{aplzl = c}aplzl</b>

WHERE b~aufnr = '000005000240'.

WRITE:/ wa_temp-aufnr, wa_temp-aufpl, wa_temp-ltxa1, wa_temp-vornr, wa_temp-arbei.

APPEND wa_temp TO it_temp.

ENDSELECT.

Message was edited by: Phani Kiran Nudurupati

Re: Problems with joining tables

RichHeilman — Mon, 13 Feb 2006 20:36:00 GMT

The reason that you need that is because of the relation between AFVC and AFVV. Notice that the keys of these tables are the same. Meaning that for every AFVC record, there is a corresponding AFVV record and they are joined by there keys AUFPL and APLZL. The way you had you code earlier, you were joining every record from AFVV with key AUFPL to each record of AFVC with key AUFPL. Now you are joining 1 for 1 to the tables. Did I confuse you more?

Regards,

Rich Heilman

Re: Problems with joining tables

Former Member — Mon, 13 Feb 2006 20:40:47 GMT

Hello again,

Thanks for the code phani, great.

Also huge thanks for the explanation Rich. You didnt confuse me at all This made understand why I got four rows.

Regards

Tomas

Re: Problems with joining tables

Former Member — Mon, 13 Feb 2006 20:46:12 GMT

Hi Tomas,

Generally when you are selecting data from the tables make sure you take the key fields in your select,so that you will not get confused when you get multiple lines of similar data.If the key is present then it is quite easy to understand the data in the internal table.

While writing as a report,you can just write those fields which are required.