CODEWITHSUNDEEP
sqlabapopensql
PeterP
PeterP

Reputation: 23

JOIN of 4 tables, how to restrict SELECT columns to one table only?

I am working on ABAP program - user input is to query column ANLAGE and output is to get all records from table EADZ (and only fields of EADZ) based on ANLAGE.

Statement and joins should work like this:

Here is the code I tried:

DATA: gt_cas_rezy   TYPE STANDARD TABLE OF eadz,
      lv_dummy_eanl LIKE eanl-anlage.
SELECT-OPTIONS: so_anl FOR lv_dummy_eanl NO INTERVALS NO-EXTENSION.

SELECT * FROM eadz 
INNER JOIN etdz ON eadz~logikzw EQ etdz~logikzw
INNER JOIN egerr ON etdz~equnr EQ egerr~equnr
INNER JOIN eastl ON egerr~logiknr EQ eastl~logiknr
INTO CORRESPONDING FIELDS OF TABLE @gt_cas_rezy
WHERE eastl~anlage IN @so_anl.

I got the records from table EADZ except that the date fields are empty (even though, they are filled in database table). I am assuming there is a problem with JOINs since in statement like this I join all the fields of all 4 tables into one "record" and then to corresponding fields of internal table.

How to get the values of date fields?

Upvotes: 2

Views: 2371

Answers (1)

peterulb
peterulb

Reputation: 2988

You can find the answer in the documentation.

If a column name appears multiple times and no alternative column name was granted, the last column listed is assigned.

In your case, at least two tables share the same column name. Therefore the values from the last mentioned table are used in the join.

You can solve this by listing the columns explicitly (or eadz~* in your case), giving an alias if required.

SELECT EADZ~* FROM EADZ INNER JOIN ETDZ ON EADZ~LOGIKZW = ETDZ~LOGIKZW
INNER JOIN EGERR ON ETDZ~EQUNR = EGERR~EQUNR
INNER JOIN EASTL ON EGERR~LOGIKNR = EASTL~LOGIKNR
INTO CORRESPONDING FIELDS OF TABLE @gt_cas_rezy
WHERE EASTL~ANLAGE IN @SO_ANL.

If you require additional fields, you can add them explicily with e.g. EADZ~*, EASTL~A.

Upvotes: 2

Related Questions