joining of 4 tables

[Former Member]

Hi experts,

Iam trying to join 4 tables.(EQUI,EQUZ,ILOA,JEST)

SELECTION-SCREEN BEGIN OF BLOCK A1 WITH FRAME TITLE TEXT-001.
SELECT-OPTIONS:S_EQTYP FOR EQUI-EQTYP, " DEFAULT 'H',
               S_VKBUR FOR ILOA-VKBUR,
               S_STATUS FOR TJ02T-TXT04.
PARAMETERS P_INBDT LIKE EQUI-INBDT DEFAULT SY-DATUM.
SELECTION-SCREEN END OF BLOCK A1.

  SELECT EQUI~EQUNR EQUI~EQTYP EQUI~EQART EQUI~INBDT
         EQUI~OBJNR ILOA~VKBUR JEST~STAT
     INTO CORRESPONDING FIELDS OF TABLE T_ET
     FROM ( ( ( ( <b>EQUI</b> INNER JOIN <b>EQUZ</b>
     ON EQUI~EQUNR = EQUZ~EQUNR )
       INNER JOIN <b>ILOA</b>
     ON EQUZ~ILOAN = ILOA~ILOAN )
       INNER JOIN <b>JEST</b>       
     ON EQUI~OBJNR = JEST~OBJNR )
     INNER JOIN TJ02T
     ON JEST~STAT = TJ02T~ISTAT )
     
           WHERE EQUI~EQTYP IN S_EQTYP AND
                 ILOA~VKBUR IN S_VKBUR AND
                 EQUI~INBDT LE P_INBDT AND
                 JEST~INACT = SPACE.

In this code iam not able to get proper data.Where iam doing wrong in select statement?

reward guaranteed,

thanks

kaki

[former_member186741 · Accepted solution]

there is nothing wrong with 'joins' and they can be coded efficiently. However great care does need to be given to the design of them and for inexperienced abappers there are lots of traps.

The most common trap is to that inner joins will not return a row if any of the links fail - if any of the joins are 'optional' they should be made 'outer' joins instead.

Another trap is to code the joins too loosely so that too many rows are returned. This can be fixed by ensuring all the key fields are specified in the join clause.

You haven't explained how the returned data is wrong but I'm guessing that some rows you expected were not there. This may be because one of you joins needs to be converted into an outer one.

[Former Member]

the better and easier way would be to use FOR ALL ENTRIES and the READ statement.just declare 5 internal tables; 4 for those database tables and 1 for the final one.

[Former Member]

Change your select statement and add <u>tj02t~spras = sy-langu.</u>.

For performance never do join on 4 tables as this is not recommended by SAP. For any inner join you need to use unique where condition else you will get multiple entries.

SELECT equi~equnr

equi~eqtyp

equi~eqart

equi~inbdt

equi~objnr

iloa~vkbur

jest~stat

INTO CORRESPONDING FIELDS OF TABLE t_et

FROM ( ( ( ( equi INNER JOIN equz

ON equi_{equnr = equz}equnr )

INNER JOIN iloa

ON equz_{iloan = iloa}iloan )

INNER JOIN jest

ON equi_{objnr = jest}objnr )

INNER JOIN tj02t

ON jest_{stat = tj02t}istat )

WHERE equi~eqtyp IN s_eqtyp AND

iloa~vkbur IN s_vkbur AND

equi~inbdt LE p_inbdt AND

jest~inact = space and

<b> tj02t~spras = sy-langu.</b>

[Former Member]

Hai try with this

SELECT EQUI~EQUNR

EQUI~EQTYP

EQUI~EQART

EQUI~INBDT

EQUI~OBJNR

ILOA~VKBUR

JEST~STAT

INTO CORRESPONDING FIELDS OF TABLE T_ET

FROM ( ( ( ( EQUI INNER JOIN EQUZ

ON EQUI_{EQUNR = EQUZ}EQUNR )

INNER JOIN ILOA ON EQUZ_{ILOAN = ILOA}ILOAN )

INNER JOIN JEST ON EQUI_{OBJNR = JEST}OBJNR )

INNER JOIN TJ02T ON JEST_{STAT = TJ02T}ISTAT )

WHERE EQUI~EQTYP IN S_EQTYP AND

ILOA~VKBUR IN S_VKBUR AND

EQUI~INBDT LE P_INBDT AND

JEST~INACT = SPACE.

Regards

Sreenu.

[Former Member]

Kaki,

Can you try removing the JEST~INACT from the where clause? Do you still don't get the data?

What exactly is the status you are looking for?

Regards,

Ravi

[Former Member]

One mistake could be because u have to add another variable in where condition.

WHERE EQUI~EQTYP IN S_EQTYP AND

ILOA~VKBUR IN S_VKBUR AND

EQUI~INBDT LE P_INBDT AND

JEST~INACT = SPACE <b>AND

TJ02T-SPRAS = SY-LANGU</b>

[Former Member]

Hi Kaki,

Performing a Join on 4 tables will reduce the performance drastically instead you try using FOR ALL ENTRIES or READ TABLE on the same tables.

I would you advice you not to use Join on 4 tables.

Thanks & Regards,

YJR.