Best way to write SELECT statement

[Former Member]

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.

[Former Member]

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.

ENDLOOP.

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

[Former Member]

Why left outer join ? Why not with inner join ?

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

[Former Member]

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?

[Former Member]

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

[Former Member]

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

[koolspy_ultimate]

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