Nested select statements.

[Former Member]

Hi, I am writing a piece of code in which i use nested select statements for 4 tables to retrieve one column's data (ie: POSNR). But when i execute it, it takes way too long to get the data. I'm quite new to ABAP so very in need of your help. Thanks

REPORT  z_impos_test.
 
TABLES: coss,       "CO Object: Cost Totals for Internal Postings
        afvc,       "Operation within an order
        prps,       "WBS (Work Breakdown Structure) Element Master Data
        imzo.       "Table: CO Object - Capital Investment Prog.Pos.
 
TYPES: BEGIN OF st_impos,
  objnr       TYPE coss-objnr,
  gjahr       TYPE coss-gjahr,
  kstar       type coss-kstar,
  projn       type afvc-projn,
  pspnr       type prps-pspnr,
  posnr       type imzo-posnr,
END OF st_impos.
 
data: year TYPE coss-gjahr value '2007'.
 
DATA: t_output  TYPE STANDARD TABLE OF st_impos WITH HEADER LINE,
      st_output TYPE st_impos,
      t_output2 TYPE STANDARD TABLE OF st_impos WITH HEADER LINE,
      st_output2 TYPE st_impos.
 
SELECT objnr gjahr kstar FROM coss
INTO CORRESPONDING FIELDS OF st_output
WHERE ( objnr LIKE 'NV%' OR
      objnr LIKE 'PR%' ) AND
       gjahr = year.
   SELECT SINGLE projn from afvc into CORRESPONDING FIELDS OF st_output
       WHERE objnr = st_output-objnr.
 APPEND st_output to t_output.
 ENDSELECT.
 
SORT t_output BY objnr.
DELETE ADJACENT DUPLICATES FROM t_output.
 
LOOP AT t_output into st_output.
SELECT objnr pspnr
       INTO CORRESPONDING FIELDS OF st_output2
       FROM prps
       WHERE objnr = st_output-objnr
       AND   pspnr = st_output-pspnr.
  SELECT SINGLE posnr from imzo into CORRESPONDING FIELDS OF st_output2
       WHERE objnr = st_output2-objnr.
APPEND st_output2 to t_output2.
ENDSELECT.
ENDLOOP.

 
LOOP AT t_output2 to st_output2.
WRITE:   st_output2-posnr.
ENDLOOP.

Edited by: Jacie Johns on Apr 23, 2009 11:26 PM

[Former Member]

You got a lot of help in the other thread. It really looks like you should assign more points to those that helped. And make sure it is marked as "solved" at the end - hopefully by awarding the best answer with 10 poin ts.

Rob

[Former Member]

Hi Rob, I did awarded to those who have a helpful answer in another thread. By the way, can you please give me some ideas about how this code can be more efficient? I also did some research on join select statement but it's hard too understand and write the code for it.

[Former Member]

Well, it doesn't look like SELECT/ENDSELECT would cause the problem. You are probably retrieving large amounts of data in the first SELECT and one of the other ones isn't using an index. That's where I'd start looking.

You aso need to look at the stick at the top of the forum

This will show you how to do some analysis yourself quite easily. (I should have asked you to look at it before posting, but since I didn't, I really can't do anything about it

Rob

[Former Member]

i can tell you about abap efficiency code .

1) sort your internal table by by using key .

2) dont use 'corresponding fields of ' in to your select query because if you are using 'select single' then no need to

use 'corresponding' as you have only one record to read.

4) If you are selecting huge amount of data better to use internal table by WA.

3) And pass where condition in flow (means first pass year).

Hope this will help you.

Regards

Shrikant

[Former Member]

It should not be difficult to change a nested SELECT with a SELECT SINGLE into a join.

This can be done for both nested selects.

And then you can add a FOR ALL ENTRIES to the second one.

And som ething is wrong in your coding:

+ your table t_ouput has three field but you select only four in the first select.

+ you must sort not only by objnr but by all field, actually by the fields which you use

later.

and this is really great 'tell me what I told you' input equal to output

SELECT objnr pspnr
       INTO CORRESPONDING FIELDS OF st_output2
       FROM prps
       WHERE objnr = st_output-objnr
       AND   pspnr = st_output-pspnr.

Overall I would you have more problem than only performance problems. The coding seems

incorrect to me.

Siegfried

[Former Member]

First do a select on table coss and for all the entries of the table coss, select from table afvc.

This way you can avoid nested select queries.

Similarly, you can do a select on prps for all entries of t_output and can avoid select inside loops.

This way the performance of the program increases.

Thanks,

Sowmya

[Former Member]

HI John,

Try to avoid INTO CORRESPONDING FIELDS in SELECT statement.

As you are not using PSPNR and POSNR fields in the first SELECT statement. If you remove these fields, in structure, INTO CORRESPONDING FIELDS can be avoided. Create a separate structure with the fields that are required for T_OUTPUT table (i.e. create another structure with only first 4 fields, as T_OUTPUT1.

And as mentioned in your code,

WHERE objnr = st_output-objnr

AND pspnr = st_output-pspnr

in select statement, ST_OUTPUT-PSPNR value is not fetched in the previous select statements.

Create Another structure with fields OBJNR, PSPNR, POSNR for table T_OUTPUT2 to store the data from tables, PRPS and IMZO.

Use JOINS and FOR ALL ENTRIES to fetch the desired data.

The sample code is as follows:

===

TYPES: BEGIN OF ty_output1,

objnr TYPE coss-objnr,

gjahr TYPE coss-gjahr,

kstar type coss-kstar,

projn type afvc-projn,

END OF ty_output1,

BEGIN OF ty_output2,

objnr TYPE coss-objnr,

pspnr type prps-pspnr,

posnr type imzo-posnr,

END OF ty_output2,

BEGIN OF ty_output3,

objnr TYPE coss-objnr,

gjahr TYPE coss-gjahr,

kstar type coss-kstar,

projn type afvc-projn,

pspnr type prps-pspnr,

posnr type imzo-posnr,

END OF ty_output3.

data: year TYPE coss-gjahr value '2007'.

DATA:

wa_output1 TYPE ty_output1,

wa_output2 TYPE ty_output2,

wa_output3 TYPE ty_output3,

t_output1 TYPE STANDARD TABLE OF ty_output1 ,

t_output2 TYPE STANDARD TABLE OF ty_output2 ,

t_output3 TYPE STANDARD TABLE OF ty_output3..

SELECT c_{objnr c}gjahr c_{kstar a}projn FROM coss as c

INNER JOIN afvc as A on a_{objnr = c}objnr

INTO table t_output1

WHERE ( c~objnr like 'NV%' or

c~objnr like 'PR%' ) and

c~gjahr = year.

SELECT p_{objnr p}pspnr i~posnr FROM prps as P

INNER JOIN imzo AS I on p_{objnr = i}objnr

INTO TABLE t_output2

for all entries in table T_OUTPUT1

WHERE p~objnr = t_output1-objnr.

SORT : t_output1 BY objnr,

t_output2 BY objnr.

DELETE ADJACENT DUPLICATES FROM : t_output1 COMPARING objnr,

t_output2 COMPARING objnr.

LOOP AT t_output1 INTO wa_output1

READ TABLE t_output2 INTO wa_output2 WITH KEY objnr - wa_output1-objnr BINARY SEARCH.

if sy-subrc = 0.

MOVE <wa_output1 fiels> and <wa_output2 fields> to WA_OUTPUT3.

append wa_output3 to T_OUTPUT3.

endif.

ENDLOOP.

Hope this will solve your problem.

Regards,

Sai Prasad

[Former Member]

Thanks everyone, you guys help a lot!. You are awarded!

And hi Sai Prasad, thank you so much for your sample code. I tried it and i changed MOVE <wa_output1 fields> and <wa_output2 fields> to WA_OUTPUT3 to

MOVE-CORRESPONDING wa_output1 to wa_output3.

MOVE-CORRESPONDING wa_output2 to wa_output3.

because it's easier for me to understand (even though it's longer)

However, there comes a problem when i go to debugger. I did the single step, look at t_output1, there are more than 100k records in the first inner join. But go to the second inner join, look at t_output2, there are no record in there. So it turns out there's no data in t_output3. Can you please explain why? (is it because there's no record matched with the condition in the 2nd inner join?)

[Former Member]

HI,

If data is not fetched into table T_OUTPUT2, no records will be moved to T_OUTPUT3. Because,

we are looping through T_OUTPUT1 and based on the key field of it, we are reading T_OUTPUT2. If the read is successfuly, they only we are moving records to T_OUTPUT3. If read is not successful, no value will be moed to T_OUTPUT3 (ofcouse, initially we are moving to work area and appeding to T_OUTPUT3).

Regards,

Sai Prasad

[Former Member]

I do not see why a full join should not work

SELECT c~objnr c~gjahr c~kstar a~projn p~pspnr i~posnr 
              INTO TABLE lt_output
              FROM coss as c 
              INNER JOIN afvc as a
              ON a~objnr = c~objnr
              INNER JOIN prps as p
              ON p~objnr = a~objnr
              INNER JOIN imzo as i
              ON i~objnr = p~objnr
              WHERE ( c~objnr like 'NV%' 
                          or c~objnr like 'PR%' )
              AND c~gjahr = year.

Siegfried

[Former Member]

hi Siegfried ,

In your piece of code, it has to be

INNER JOIN prps as p

ON p_{pspnr = a}proj

instead of

INNER JOIN prps as p

ON p_{objnr = a}objnr

Is it right?

Also, i need to truncate the PROJN number after 9 characters, so does it make more sense if i do 2 separate INNER JOINs? . For example, one PROJN data is P: 11979 - T - E. I just want to get P: 11979 - T.

[Former Member]

sorry, I did not go into details, I just look at the porposal some postings above mine,

there was only objnr.

Actually you will probably need two ON-conditions, but all you original question is not clear

in that.

You must try it in your system.

Siegfried

[Former Member]

so this is what i did based on your code.

SELECT c~objnr c~gjahr c~kstar a~projn p~pspnr i~posnr
              INTO TABLE t_output
              FROM coss as c
              INNER JOIN afvc as a
              ON a~objnr = c~objnr
              INNER JOIN prps as p    ''  NOTE
              ON p~pspnr = a~projn
              INNER JOIN imzo as i
              ON i~objnr = p~objnr
              WHERE ( c~objnr like 'NV%'
                          or c~objnr like 'PR%' )
              AND c~gjahr = year.

I tested it and there's no data retrieved in t_ouput. I think the problem is from INNER JOIN PRPS, because when i went to se11, i tried some values connected to each other for every table, i noticed that the projn number in AFVC need to be truncated after 9 characters so that when i passed this number into field pspnr in table PRPS, it will give me the corresponding values ( otherwise if i leave the projn number as default, there's no corresponding values retrieved). That's why i ask you how to truncate the characters in field projn if I do the one full JOIN or i need to do two JOINs?

Edited by: Jacie Johns on Apr 27, 2009 5:47 PM