Which is the best way to select the desired data

Hi All,

I am trying to find out the best select query for selecting IDoc data from both EDIDC & EDIDS table.

The desired fields to be selected are:

EDIDC-docnum

EDIDC-status

EDIDC-direct

EDIDS-countr ( highest countr - trying to select the latest status )

EDIDS-statxt ( corresponding status text of the highest countr )

Given conditions are:

IDoc numbers (SELECT-OPTIONS o_idoc FOR edidc-docnum.)

Last Changed by (SELECT-OPTIONS o_lc_by FOR user_addr-BNAME NO INTERVALS.)

My current select query is:

SELECT *
  INTO CORRESPONDING FIELDS OF TABLE i_edidc
  FROM edidc AS A
 INNER JOIN edids AS B
    ON A~docnum = B~docnum
 WHERE A~docnum  IN o_idoc
   AND A~idoctp  IN o_idoctp "additional condition basic type
   AND A~credat  IN o_ct_dat "additional condition created date
   AND A~cretim  IN o_ct_tim "additional condition created time
   AND A~upddat  IN o_lc_dat "additional condition updated date
   AND A~updtim  IN o_lc_tim "additional condition updated time
   AND B~uname   IN o_lc_by
   AND B~countr = ( select max( countr ) from edids where docnum = A~docnum ).

There can be an alternative approach that:

- first selecting the EDIDC records into one internal table (i_edidc) separately based on IDoc numbers and other conditions (like basic type, dates etc..)

- then selecting the EDIDS records into i_edids using the internal table i_edidc.

- finally, checking the user (last changed by) condition and deleting the not matched records from both internal tables.

Please give your best solution.

Thanks in advance.

Regards,

Sukhbold.

Edited by: Sukhbold Altanbat on Oct 13, 2008 12:03 PM