Internal table query

[Former Member]

Hi experts,

I've created an internal table with fields CUOBJ and OBJEK from table INOB. I then select entries with key IFLOT-TPLNR = INOB-OBJEK. This is slowing my report down considerably.

Would it be possible to create the internal table containing only entries where IFLOT-FLTYP = 'B' ?

This would reduce the number of entries in the internal table significantly. If it can be done please could someone advise how?

Many thanks

Dave

[adrian_mejido]

Hi Dave,

You can try to do an INNER-JOIN of the two databases.

Best Regards.

[Former Member]

Hi Adrian,

I already have some tables joined in SQ02. The data comes from different functional location levels, so I can't do another join to IFLOT and then return only data where IFLOT-FLTYP = 'B'.

Is it possible to do joins on internal tables?

Kind regards

Dave

[adrian_mejido]

At that case:

• You can try to do a select with FOR ALL ENTRIES OF

                   SELECT *
                        FROM iflot
                        INTO CORRESPONDING FIELDS OF TABLE lt_iflot
                        FOR ALL ENTRIES IN lt_inob
                            WHERE TPLNR = lt_inob-objek
                                  AND FLTYP = 'B'.

Best Regards

[Former Member]

Thank you so much Adrian!

Kind regards

Dave

[adrian_mejido]

This message was moderated.

[Former Member]

I marked your reply as a correct answer but now it's not showing this or giving me the option to mark answers as helpful! I'll try on a different browser at home later on.

I have a problem with the code,

When you use the addition "FOR ALL ENTRIES IN itab", the fields"OBJEK" and "IT_IFLO2-TPLNR" must have the same type and the same length.

iflo-tplnr is 30 characters in length but inob-objek is 50 characters. Would it be possible to define iflo-tplnr as 50 characters?

Kind regards

Dave

[former_member187748]

Hi Dave,

your question was of basic level, so moderator has changes it from question to discussion, so you will not able to reward anyone.

Its not your browser problem.

[Former Member]

Hi Dave,

we can avoid the error by giving an offset , you can ignore the warning

write the Select statement as below:

SELECT * FROM iflot

INTO CORRESPONDING FIELDS OF TABLE lt_iflot

FOR ALL ENTRIES IN lt_inob

WHERE TPLNR = lt_inob-objek+0(30)

AND FLTYP = 'B'.

[Former Member]

Hi Ramya,

Thanks very much! I'll give it a go.

[Former Member]

Hi Adrian,

Using this method works but hasn't sped up my report. Please is there anything else you could suggest that might make it more efficient? When i go through the steps to get the data via SE16 everything runs very quickly, so I'm puzzled as to why it's so slow in my report. Could it be because the key field in INOB is CUOBJ and I'm selecting entries bassed on OBJEK?

Is it right that an internal table can not be joined to a database table?

This is my code below:

Data Section:

data:      begin of it_iflo2 occurs 0,
      tplnr(50),
        end of it_iflo2.

data:  it_inob type table of inob,
         st_inob type inob.

Initialization Section:

select tplnr from iflo
  into corresponding fields of table it_iflo2
  where fltyp = 'B'.

select cuobj objek from inob
  into corresponding fields of table it_inob
  for all entries in it_iflo2
  where objek = it_iflo2-tplnr.

Additional Field Code:

clear obj2.
read table it_inob into st_inob
with key objek = iloa-tplnr(11)
binary search.
if sy-subrc = 0.
  obj2 = st_inob-cuobj.
  endif.

Kind regards

Dave

[adrian_mejido]

Hi Dave,

Creating index of your Database tables might help you.

Best Regards

[Former Member]

Hi Adrian,

I created an additional field for iloa-tplnr(11) and referred to this and that has sped it right up. Thanks again for your help!

[Former Member]

SELECT a~cuobj a~objek
  INTO TABLE lt_inob
    FROM inob AS a INNER JOIN iflot AS b
  ON a~tplnr eq b~tpnlr
  AND b~fltyp eq 'B'.

[Former Member]

If you have performance problems you could use a View instead of a join.

[Former Member]

Thnak you Vincenzo, please can you elaborate on that?

[Former Member]

Well I mean something like that:

Database Views - BC - ABAP Dictionary - SAP Library

Views (SAP Library - BC - ABAP Dictionary)

So, "...the data is actually selected in the database. Since the join operation is executed in the database in this case, you can minimize the number of database accesses in this way."

Anyway take a look to SE30 transaction, execute and evaluate your result. Also take a look to trace (st03 for example)

For a report like that:

my report

PARAMETERS p_cityfr TYPE spfli-cityfrom. DATA: BEGIN OF wa,          carrid   TYPE scarr-carrid,          carrname TYPE scarr-carrname,          connid   TYPE spfli-connid,        END OF wa,        itab LIKE SORTED TABLE OF wa                  WITH NON-UNIQUE KEY carrid. SELECT s~carrid s~carrname p~connid         INTO CORRESPONDING FIELDS OF TABLE itab         FROM scarr AS s         LEFT OUTER JOIN spfli AS p ON s~carrid   =  p~carrid                                    AND p~cityfrom = p_cityfr. LOOP AT itab INTO wa.    IF wa-connid = '0000'.      WRITE: / wa-carrid, wa-carrname.    ENDIF. ENDLOOP.

On SE30 I see:

On ST03 I see:

The "explain" like the Oracle command is very useful to evaluate your situation.

Bye

[Former Member]

Thank you so much for posting this information. Very useful