Select query between an internal table and 3 joined db tables.

[former_member823860]

Hello,

Need some help pls! I need to add another column to my internal table which should contain the status of the equipment (TJ02T-ISTAT).

I have the equipment number in my internal table itab1-equnr (type of equi-equnr).

The relationship between the db tables is as follows:

Itab1~equnr eq equi~equnr

equi~objnr eq jest~objnr

jest-stat eq tj02t~istat (status of the equipment that I need to add next to the equimpent number column) .

How do I do the select query in this case?

I've tried something like this, but didn't work out.

Select * from EQUI INNER JOIN 
              JEST ON
              EQUI-OBJNR EQ JEST-OBJNR
                   INNER JOIN
              TJ02T ON 
              JEST-STAT EQ TJ02T-ISTAT
  FOR ALL ENTRIES IN @itab1
  WHERE equnr = @itab1-equnr AND
        spras = 'EN'
  INTO CORRESPONDING FIELDS OF @WA_itab1.
  MODIFY itab1 FROM WA_itab1 TRANSPORTING istat.
  ENDSELECT.

[former_member823860]

Anyone, any ideas?

[Sandra_Rossi]

As it's your first join in ABAP SQL, avoid complex things: start by joining 2 tables, without for all entries, then try to solve the syntax errors.

[Sandra_Rossi]

Note that 'EN' is incorrect, because language code is stored on 1 character in database tables, not 2 characters. It's only when you use Conversion Exits (explicitly or implicitly as with WRITE) that the 1-character language code is rendered as 2 characters.

[SugguSandeep]

Hi rapadungadunga2

Firstly go through syntax format & check whether they are in proper way or not Query For 3 Joined Database Tables

SELECT <field1> <field2> <field3>..... Or * ( According To Your Requirement )
INTO TABLE <INTERNAL TABLE>
FROM <TABLE1> INNERJOIN <TABLE2>
ON <TABLE1>~<Common Field> = <TABLE2>~<Common Field>
INNER JOIN <TABLE3>
ON <TABLE2>~<Common Field> = <TABLE3>~<Common Field>

I hope this helps you :)Thanks, Suggu Sandeep.

[former_member823860]

Thanks, Suggu!

[raymond_giuseppi]

Suggestions:

• Replace the 'EN' with sy-langu (will also avoid conversion problem, look at domain definition)
• Put the result of the select into another temporary internal table, only keep required fields (e.g. OBJNR and ISTAT) for memory and performance
• Move the update of the internal table out of the SELECT statement (guess which record is ypdated, also remove any HEADER LINE to internal table, and use a sorted type for the new internal table for performance)
• etc.

[former_member823860]

Thank you, Raymond!

[former_member823860]

Hello Sandra,

Thanks, I have discovered the reason of the error in the end.

It occurred because I've used "-" instead of "~" when I did the join.

Thank you for the hints!