topic Best way to write SELECT statement in Application Development and Automation Discussions

Best way to write SELECT statement

Former Member — Wed, 29 Jun 2011 15:29:47 GMT

Hi,

I am selecting fields from one table, and need to use two fields on that table to look up additional fields in two other tables.

I do not want to use a VIEW to do this.

I need to keep all records in the original selection, yet I've been told that it's not good practice to use LEFT OUTER joins. What I really need to do is multiple LEFT OUTER joins.

What is the best way to write this? Please reply with actual code.

I could use 3 internal tables, where the second 2 use "FOR ALL ENTRIES" to obtain the additional data. But then how do I append the 2 internal tables back to the first? I've been told it's bad practice to use nested loops as well.

Thanks.

Re: Best way to write SELECT statement

Former Member — Wed, 29 Jun 2011 15:45:51 GMT

For Nested Loop use PARALLEL METHOD.

EX:

LOOP AT int1 .

LOOP AT int2 FROM w_index.

IF int1-key NE int2-key.

w_index = sy-tabix.

EXIT.

ENDIF.

ENDLOOP.

Edited by: avis_ramuk_g on Jun 29, 2011 5:52 PM

Re: Best way to write SELECT statement

Former Member — Wed, 29 Jun 2011 16:01:42 GMT

Why left outer join ? Why not with inner join ?

http://www.sap-img.com/abap/inner-joins.htm

Re: Best way to write SELECT statement

Former Member — Wed, 29 Jun 2011 16:20:00 GMT

An Inner Join only returns records where the key is populated for both tables.

I want to pull a group of records from one table. Where the field IS populated, I want to look up another value from another table based on what is in that field. I still want to keep the records where the field IS NOT populated. An inner join would eliminate those records. Make sense?

Re: Best way to write SELECT statement

Former Member — Wed, 29 Jun 2011 16:55:49 GMT

Ok, ok... now i understand your issue....

Then, may be, you need to use 3 internal tables... you can do a loop to the first table and read tables to the others within this loop in order to complete the data.... you need to have some "key" fields in all these internal tables...

let me think if it's possible to do only with select statements...

Re: Best way to write SELECT statement

Former Member — Thu, 30 Jun 2011 04:31:06 GMT

Hi,

in your case having 2 internal table to update the one internal tables.

do the following steps:


*get the records from tables
sort: itab1 by key field,  "Sorting by key is very important
      itab2 by key field.  "Same key which is used for where condition is used here
loop at itab1 into wa_tab1.
  read itab2 into wa_tab2     " This sets the sy-tabix
       with key key field = wa_tab1-key field
       binary search.
  if sy-subrc = 0.              "Does not enter the inner loop
    v_kna1_index = sy-tabix.
    loop at itab2 into wa_tab2 from v_kna1_index. "Avoiding Where clause
      if wa_tab2-keyfield <> wa_tab1-key field.  "This checks whether to exit out of loop
        exit.
      endif.

****** Your Actual logic within inner loop ******

   endloop. "itab2 Loop
  endif.
endloop.  " itab1 Loop

Refer the link also you can get idea about the Parallel Cursor - Loop Processing.

http://wiki.sdn.sap.com/wiki/display/Snippets/CopyofABAPCodeforParallelCursor-Loop+Processing

Regards,

Dhina..

Re: Best way to write SELECT statement

koolspy_ultimate — Fri, 01 Jul 2011 11:19:54 GMT

try


sort: lt_vbpa by kunnr,  
      lt_kna1 by kunnr. 
loop at lt_vbpa into wa_vbpa.
  read lt_kna1 into wa_kna1    
       with key kunnr = wa_vbpa-kunnr
       binary search.
  if sy-subrc = 0.           
    v_kna1_index = sy-tabix.
    loop at lt_kna1 into wa_kna1 from v_kna1_index. 
      if wa_kna1-kunnr <> wa_vbpa-kunnr.  
        exit.
      endif.

****** Your Actual logic within inner loop ******

   endloop. "KNA1 Loop
  endif.
endloop.  " VBPA Loop