Which is the best way to select the desired data
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:
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.
Edited by: Sukhbold Altanbat on Oct 13, 2008 12:03 PM