Column specification with inner join

Development (ABAP Development WorkBench, ABAP/4 programming)

Moderators: Snowy, thx4allthefish, YuriT, Gothmog

Post Reply
Sharpshooter
Posts: 1172
Joined: Wed Mar 17, 2010 12:01 pm
Location: In the dark

Column specification with inner join

Post by Sharpshooter » Fri Nov 15, 2013 8:25 am

OK - I feel like I should either know this or be able to find the answer, but I have come up empty.
Consider the following select statement:

Code: Select all

      select * into corresponding fields of table itab 
        from mara inner join marc on
        mara~matnr = marc~matnr
        where..............
If a field with the same name exists in both tables, is there a way to specify which one gets read and stored in the corresponding itab field?
For example HERKR exists in both mara and marc.
Based on my testing, the last table in the join seems to win, so I get the value from marc-herkr in my result.
1) Is this consistent behaviour or is it random hit and miss?
2) Is there a way to explicitly specify which table a single column should come without naming all columns (itab is basically defined as MARC, mara is only in the join to facilitate 'where' conditions).

I did read this in the ABAP knowledge corner which seems to say that I really should name all fields (with table name) I want in the result:
You can, if you wish leave out the table names on the fields if they are unique to that table (ie they do not occur in any other table in the join), however I always include them to make sure that there is no doubt where the data is coming from.
Any hints or suggestions are appreciated.
Thanks in advance!
Dave
Good luck!

VLozano
Posts: 5142
Joined: Mon Sep 13, 2004 8:17 am
Location: Idiocity
Contact:

Re: Column specification with inner join

Post by VLozano » Fri Nov 15, 2013 9:06 am

Bill them twice because you'll have to work twice.

Sadly, I guess you'll have no other option than to name the fields :?
Tuly Idiots
Because we know we are part of the problem

Rich
Posts: 7116
Joined: Thu Oct 31, 2002 4:47 pm
Location: Liverpool
Contact:

Re: Column specification with inner join

Post by Rich » Mon Nov 18, 2013 8:28 am

If you want both then alias the fields as well.
Regards

Rich

Image
Abap KC:http://www.richard-harper.me.uk/Kb
SFMDR:http://www.se37.com

Gothmog
Posts: 1952
Joined: Wed Sep 12, 2007 4:46 am
Location: Probably not home

Re: Column specification with inner join

Post by Gothmog » Mon Nov 18, 2013 8:51 am

Sharpshooter wrote:1) Is this consistent behaviour or is it random hit and miss?
2) Is there a way to explicitly specify which table a single column should come without naming all columns (itab is basically defined as MARC, mara is only in the join to facilitate 'where' conditions).
1) This is a consistent behavior (due to INTO CORRESPONDING FIELDS).
SAP Help on INTO CORRESPONDING FIELDS wrote:If a column name appears multiple times and no alternative column name was specified, the last column listed is assigned.
2) I don't think so, unfortunately.
SAP Help on SELECT - columns wrote:If multiple database tables are specified after FROM, you cannot prevent multiple columns from getting the same name when you specify *.
EDIT: Well, if you don't need any field from MARA, you could try using a subquery instead of a join:

Code: Select all

SELECT * FROM marc
  INTO CORRESPONDING FIELDS OF TABLE itab
  WHERE matnr IN ( SELECT matnr FROM MARA WHERE ... )
    AND ...
68 74 74 70 3a 2f 2f 74 69 6e 79 75 72 6c 2e 63 6f 6d 2f 62 64 6f 37 6d 77 67

Sharpshooter
Posts: 1172
Joined: Wed Mar 17, 2010 12:01 pm
Location: In the dark

Re: Column specification with inner join

Post by Sharpshooter » Mon Nov 18, 2013 1:52 pm

Thanks to all.

Dave
Good luck!

Post Reply