left outer join with only 1 line of the item lines

[Former Member]

Hello,

maybe for some ppl an easy question:

I have an header table and an item table.

I have to gather some transparant table fields into an internal table.

In this internal table there has to be some headerfields and (and if itemlines exists) also some item fields of the FIRST item line.

I think the best way to do achieve this is an left outer join.

But i do not know how to take care that there is only one and the first item line selected for this

what has to be changed on the following (example) coding.

types: begin of ty_itab,

    afield1(10)  type c,

    afield2(10)  type c,

    afield3(10)  type c,

    bfield1(10)  type c,

    bfield2(10)  type c,

          end of  ty_itab.

data: gt_itab type table of ty_itab.

.

select a~afield1 a~afield2 a~afield3  b~bfield1 b~bfield2

   into corresponding fields of table gt_itab

   from header_tab as a

   left outer join item_tab as b on a~docid eq b~docid

   for all entries in gt_itab2

   where a~docid eq gt_itab2-docid

Thanx in advance

[former_member206650]

Hi frank,

if you require all the entries of header and common data between header and item left outer join will work,

but if you can try to avoid it by writing two select queries.

i couldn't get what is gt_itab2

can you try this code

types: begin of ty_itab,

    afield1(10)  type c,

    afield2(10)  type c,

    afield3(10)  type c,

    bfield1(10)  type c,

    bfield2(10)  type c,

          end of  ty_itab.

data: gt_itab type table of ty_itab.

.

select a~afield1 a~afield2 a~afield3  b~bfield1 b~bfield2

   into corresponding fields of table gt_itab

   from header_tab as a

   left outer join item_tab as b on a~docid eq b~docid.

hope it helps...

[Former Member]

Hi Vishnu,

I see in your coding no difference (except the missing 2 last lines).

with my coding. I need this last 2 lines for the slection.

gt_itab2 is an itab with a lot of docid's.

[former_member206650]

Hi frank ,

loop at item_tab into ls_item_tab.

read table header_tab into ls_header where header_tab-docid = item_tab-docid.

//transfer necessary fields into ls_header from ls_item_tab

append ls_header into lt_header.

endloop.

loop at gt_itab2 into ls_itab2.

read table lt_header into ls_header where lt_header

//transfer necessary fields into ls_header from ls_itab2 .

endloop.

make the structure of the lt_header and ls_header accordingly.

hope its correct.

vishnu

[Former Member]

After the SELECT, sort the table by afield1 and bfield1.

Then DELETE ADJACENT DUPLICATES FROM gt_itabm COMPARING afield1 bfield1.

Rob

[Former Member]

Hello Rob,

Thanks for your answer.

i know i can delete the duplicates afterwarts. but i want to do it in the select to decrease processing time.

I am rewriting some code with performance issues.

Regards.

[Former Member]

Generally, it's best to let the SELECT do the data retrieval and leave the processing to ABAP.

Rob

[VenkatRamesh_V]

Hi,

left outer join leads to performance issue  instead split the select query using  for all entries.

Data lv_falg.

SORT item_tab by docid.

loop at item_tab into  wa_item_tab.

clear lv_falg.

AT NEW docid.

lv_flag = 'X'.

ENDAT.

if lv_falg is not initial.

read table header_tab into wa_header_tab with key docid  = wa_item_tab-docid.

if sy-subrc = 0.

wa_item_tab-bfield1   = wa_header_tab-bfield1

wa_item_tab-bfield2   = wa_header_tab-bfield2.

modify item_tab from wa_item_tab transporting bfield1 bfield2.

endif.

endloop.

Hope it helpful,

Regards,

Venkat.V

[Former Member]

Hi venkat

Thanks for your answer.

your solution will not work in my case cause i am working with DB-tables.

I am more interested in your opinion that left outer join leads to performance issue.

One of the reason i try this is because of an perfpormance issue.

the old coding was something like

Select * from ... into ls_....    

where   ....

      single select *

         from ... into ls_....    

         where   ....

abap bla bla

endselect

i want this to combine in 1 i/o statement (instead of several thousand) and then loop over the result

so im interested if my solution is a proper one.

Gr., Frank