topic Re: Internal table query in Application Development and Automation Discussions

Internal table query

Former Member — Thu, 13 Feb 2014 11:01:19 GMT

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

Re: Internal table query

adrian_mejido — Thu, 13 Feb 2014 11:04:24 GMT

Hi Dave,

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

Best Regards.

Re: Internal table query

Former Member — Thu, 13 Feb 2014 11:12:05 GMT

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

Re: Internal table query

Former Member — Thu, 13 Feb 2014 11:14:44 GMT

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'.

Re: Internal table query

adrian_mejido — Thu, 13 Feb 2014 11:22:03 GMT

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

Re: Internal table query

Former Member — Thu, 13 Feb 2014 11:31:15 GMT

Thank you so much Adrian!

Kind regards

Dave

Re: Internal table query

adrian_mejido — Thu, 13 Feb 2014 12:22:48 GMT

This message was moderated.

Re: Internal table query

Former Member — Thu, 13 Feb 2014 12:53:57 GMT

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

Re: Internal table query

former_member187748 — Thu, 13 Feb 2014 13:05:07 GMT

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.

Re: Internal table query

Former Member — Thu, 13 Feb 2014 13:24:38 GMT

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

Re: Internal table query

Former Member — Thu, 13 Feb 2014 13:29:37 GMT

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'.

Re: Internal table query

Former Member — Thu, 13 Feb 2014 14:07:53 GMT

Hi Ramya,

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

Re: Internal table query

Former Member — Thu, 13 Feb 2014 14:08:41 GMT

Thnak you Vincenzo, please can you elaborate on that?

Re: Internal table query

Former Member — Wed, 19 Feb 2014 15:57:42 GMT

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

Re: Internal table query

adrian_mejido — Wed, 19 Feb 2014 16:01:35 GMT

Hi Dave,

Creating index of your Database tables might help you.

Best Regards

Re: Internal table query

Former Member — Mon, 24 Feb 2014 17:47:31 GMT

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.

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

Re: Internal table query

Former Member — Tue, 25 Feb 2014 11:26:58 GMT

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!

Re: Internal table query

Former Member — Tue, 25 Feb 2014 11:28:57 GMT

Thank you so much for posting this information. Very useful